Re: "top5" problem

Article View: bit.listserv.sqlinfo

Article #230

Back to bit.listserv.sqlinfo Previous Next

#230

From: Roland Zumkeller
Date: Mon, 07 Aug 2000 00:00

23 lines
1017 bytes

Hi Albert,

I assume that your timestamp is unique, if it's not use an artifical
primary key for the subquery.

SELECT * INTO tbl_top FROM tbl_values AS a
WHERE
timestamp IN
(SELECT TOP 5 timestamp FROM tbl_values AS b
WHERE b.label = a.label
ORDER BY timestamp DESC)
ORDER BY label, timestamp DESC

The second one is easy:

SELECT label, (sum(value) - min(value) - max(value)) / 3 
FROM tbl_top 
GROUP BY label

I hope this helped.


Roland

Technical Details

Message-ID: <398DEB6F.E3D5E1C3@web.de>

Path: arc1-ca.pugleaf.net!unknown!not-for-mail

References: <39892AA9.F0AB7FF2@swf.feldkirch.com>

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 11355a,11189d3e354db813
X-Google-Attributes: gid11355a,public
From: Roland Zumkeller <Roland.Zumkeller@web.de>
Subject: Re: "top5" problem
Date: 2000/08/07
Message-ID: <398DEB6F.E3D5E1C3@web.de>
X-Deja-AN: 655213442
Content-Transfer-Encoding: 7bit
References: <39892AA9.F0AB7FF2@swf.feldkirch.com>
X-Accept-Language: de
Content-Type: text/plain; charset=us-ascii
X-Trace: fu-berlin.de 965602190 6414533 62.54.4.184 (16 [43476])
Mime-Version: 1.0
Newsgroups: bit.listserv.sqlinfo

Reply to Parent Message