query help: need to return 2nd from top record

[Toucan]

i need to retrieve the most recent timestamped records with unique
names (see working query below)

what i'm having trouble with is returning the next-most-recent records
(records w/ id 1 and 3 in this example)

i also need to return the 3rd most recent, 4th, 5th and 6th most recent
- i figure if i can get the 2nd working, 3rd, 4th, etc will be cake

thanks,
brett

-- create and populate table
drop table atest
create table atest(id int not null, name char(10), value char(10),
timestamp datetime)

insert into atest values (1,'a','2','1/1/2003')
insert into atest values (2,'a','1','1/1/2004')
insert into atest values (3,'b','2','1/1/2003')
insert into atest values (4,'b','3','1/1/2002')
insert into atest values (5,'b','1','1/1/2004')

-- select most recent records with distinct "name"s
select a.* from atest as a
where a.id = (select top 1 b.id from atest as b
where b.name = a.name
order by timestamp desc )

/*
query results for above query (works like a charm)
2 a 1 2004-01-01 00:00:00.000
5 b 1 2004-01-01 00:00:00.000
*/

[Hugo Kornelis]

On 23 Nov 2004 11:36:10 -0800, Toucan wrote:

i need to retrieve the most recent timestamped records with unique
names (see working query below)

what i'm having trouble with is returning the next-most-recent records
(records w/ id 1 and 3 in this example)

i also need to return the 3rd most recent, 4th, 5th and 6th most recent
- i figure if i can get the 2nd working, 3rd, 4th, etc will be cake

Hi Brett,

Here's a solution that works for 1st, 2nd, etc and also avoids the
proprietary T-SQL TOP keyword:

DECLARE @Rank int
SET @Rank = 2
SELECT a.*
FROM atest AS a
WHERE (SELECT COUNT(*)
FROM atest AS b
WHERE b.name = a.name
AND b.timestamp >= a.timestamp) = @Rank

By the way - try to avoid using reserved words as columns names!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Toucan]

thanks!
that works almost perfectly
there's one case where it fails

insert into atest values (6,'b','xxx','1/1/2004')

set rank to 1

run query and no rows are returned for the "b" rows

any other ideas?
brett

ps. i'll do better naming my columns next time :^)

[Hugo Kornelis]

On 23 Nov 2004 14:29:43 -0800, Toucan wrote:

thanks!
that works almost perfectly
there's one case where it fails

insert into atest values (6,'b','xxx','1/1/2004')

set rank to 1

run query and no rows are returned for the "b" rows

Hi Brett,

And I guess that 2 rows will be returned when yoou set rank to 2. This is
because there is a tie: the rows with id 5 and 6 tie for most-recent
timestamp. You didn't include this possibility in your original post. Now
that you did, you still didn't specify what results you want. So before I
can tweak the query, I need some answers:

* With this data, would you want to see the row with id 5, with id 6 or
both if you use @rank = 1
* Which row(s) would you want if you set @rank to 2, 3, ... with this
data?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Toucan]

Hey Hugo,

sorry for not being clear - it didn't occur to me until i saw the ">="
in the query

you're correct, 2 rows are returned when rank = 2

what i'd like to see:

rank 1: either 5 or 6 (tie broken arbitrarily)
rank 2: whichever row lost the tie-break
rank 3: row 3

thanks again for all your help,
brett

[Hugo Kornelis]

On 23 Nov 2004 15:47:32 -0800, Toucan wrote:

what i'd like to see:

rank 1: either 5 or 6 (tie broken arbitrarily)
rank 2: whichever row lost the tie-break
rank 3: row 3

thanks again for all your help,

Hi Brett,

Well, there is no such thing as an "arbitraril y" choice in SQL Server,
you'll have to tell SQL Server how to decide (or else it will return
either all rows or none, as you see). Of course, YOU can make an arbitrary
choice, but there has to be SOME choice, it has to be based on data in the
table and it has to be such that there will never be cases where there
still remains a tie.

As an example: if you decide to use "lowest ID value" as tiebreaker (you
didn't include PRIMARY KEY or UNIQUE in the table, but name and sample
data suggest that this is a key, therefor it would satisfy the condition
that no two rows will ever tie with this extra condition):

DECLARE @Rank int
SET @Rank = 1
SELECT a.*
FROM atest AS a
WHERE (SELECT COUNT(*)
FROM atest AS b
WHERE b.name = a.name
AND ( b.timestamp > a.timestamp
OR (b.timestamp = a.timestamp and b.id < a.id))) = @Rank - 1
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Toucan]

hugo,

perfect!

thank you so much
if you're ever in austin, tx, i'll buy you a beer!

brett