Advice on query

Hi,

Quite possibly this is easy but my brain is bazango'd right now and for the life of me I can't think of a good way to get the following...

I have a table with 4 columns

ID (int)
User (varchar(200))
Timestamp (datetime)
Note (text)

The ID, User, Timestamp make up the primary key and nulls are not permitted in any column

I'm trying to select the top 3 most recent rows [using timestamp] for each ID.

anyone?

thanks in advance
Mac

Hi,

SELECT *

FROM

(

SELECT

[id], [user],[timestamp], --you should not use reserved words for column names

RANK() OVER (PARTITION BY id,user,timestamp ORDER BY Timestamp DESC) AS Ranked

) Subquery

WHERE Ranked <=3

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Mac:

Maybe something like this?

set nocount on

declare @.mockUp table
( ID integer not null,
[USER] varchar (200) not null,
Timestamp datetime not null,
Note text not null
)

insert into @.mockUp values (1, 'Mugambo', '10/1/6', 'Note #1')
insert into @.mockUp values (2, 'Mugambo', '10/2/6', 'Note #2')
insert into @.mockUp values (3, 'Mugambo', '10/5/6', 'Note #3')
insert into @.mockUp values (4, 'Mugambo', '10/4/6', 'Note #4')
insert into @.mockUp values (5, 'Mugambo', '10/8/6', 'Note #5')

insert into @.mockUp values (11, 'Herman Munster', '10/1/6', 'Note #1')
insert into @.mockUp values (12, 'Herman Munster', '10/2/6', 'Note #2')
insert into @.mockUp values (13, 'Herman Munster', '10/3/6', 'Note #3')
insert into @.mockUp values (14, 'Herman Munster', '10/3/6', 'Note #4')
insert into @.mockUp values (15, 'Herman Munster', '10/5/6', 'Note #5')

select [user],
id,
Timestamp,
Note
from (
select id,
[user],
Timestamp,
rank ()
over (partition by [user] order by timestamp, id)
as Seq,
Note
from @.mockUp
) x
where seq <= 3

-- --
-- SQL Server 2005 SAMPLE OUTPUT:
-- --

-- Herman Munster 11 2006-10-01 00:00:00.000 Note #1
-- Herman Munster 12 2006-10-02 00:00:00.000 Note #2
-- Herman Munster 13 2006-10-03 00:00:00.000 Note #3
-- Mugambo 1 2006-10-01 00:00:00.000 Note #1
-- Mugambo 2 2006-10-02 00:00:00.000 Note #2
-- Mugambo 4 2006-10-04 00:00:00.000 Note #4

select x.ID,
x.[User],
x.Timestamp,
y.note
from (
select a.id,
a.[user],
a.Timestamp,
count(*) as Seq
from @.mockUp a
inner join @.mockUp b
on a.[user] = b.[user]
and ( a.timestamp > b.timestamp or
a.timestamp = b.timestamp and
a.id >= b.id
)
group by a.id, a.[user], a.Timestamp
) x
inner join @.mockUp y
on x.seq <= 3
and x.id = y.id
order by x.[user], x.seq

-- --
-- SQL Server 2000 SAMPLE OUTPUT:
-- --

-- Herman Munster 11 2006-10-01 00:00:00.000 Note #1
-- Herman Munster 12 2006-10-02 00:00:00.000 Note #2
-- Herman Munster 13 2006-10-03 00:00:00.000 Note #3
-- Mugambo 1 2006-10-01 00:00:00.000 Note #1
-- Mugambo 2 2006-10-02 00:00:00.000 Note #2
-- Mugambo 4 2006-10-04 00:00:00.000 Note #4

|||I definitely agree with Jens' solution!|||Jens, why didn't you used a TOP / ORDER BY solution ? does the "ranking over" performs better ?|||

thank you both, exactly what I was needing.

|||

Hi,

That should be worth a try for the original poster:

SELECT *

FROM SomeTable ST
WHERE Exists

(

SELECT
TOP 3 [id], [user],[timestamp], --you should not use reserved words for column names
From SomeTable Subquery
Where
Subquery.[id] = ST.[id] AND
Subquery.[user] = ST.[user] AND
Subquery.[timestamp]
= ST.[timestamp]

)

I don′t know if you meant TOP 3 in the upper query, that would not take the effect to get back the first three OF EVERY ID.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de