Returning top 2 results from each different data in a row and inserting into new table

Hi All,

I'd much appreciate any help with the following problem. I need a statement, cursor, anything....

I have a table in MS SQL Server containing the following (simplified version of course :)):

PID DATE OTHER INFO
aaa 31/05/2005 blah blah blah
aaa 06/06/2004 moo moo moo
aaa 17/05/2005 baa baa baa baa
bbb 31/04/2004 niegh neigh neigh
bbb 30/04/2004 bleet bleet
ccc 01/05/2005 oink oink
ccc 03/05/2005 cluck cluck
ccc 02/05/2005 meep meep
...etc etc etc

My challenge:

For each unique PID, sorted by date order descending (NOW first ... END DATE bottom), I need to return the top 2 results for each PID and insert them into a NEW table.

For example, using my simplified data above, the results I would exepect in my NEW tablet would be:

PID DATE OTHER INFO
aaa 31/05/2005 blah blah blah
aaa 17/05/2005 baa baa baa baa
bbb 31/04/2004 niegh neigh neigh
bbb 30/04/2004 bleet bleet
ccc 03/05/2005 cluck cluck
ccc 02/05/2005 meep meep

Note: It has returned the two most recent results for each unique PID.

I'm thinking it may need a cursor of some kind, but I just can't get to grips with what needs to be done, lord knows I've tried for the last two days.

Any help sincerely appeciated!

Rob

Hi
I'll try to help you using a cursor but there is a solution without it I don have it now

create table #result(PID char(3) ,Date datetime , otherinfo varchar(50))
declare @.id char(3)
declare c cursor for
select distinct PID from tblInfo
open c
fetch next from c into @.id
while @.@.fetch_Status = 0
begin
insert into #result(pid,date,otherinfo)
select top 2 pid , Date,otherinfo from tblInfo
Where PID=@.id
order by Date desc
fetch next from c into @.id
end
close c
deallocate c
select * from #result

I assumed that your SQL server is not case-sensetive and your table id tblInfo
|||Perfect - thank you so much!|||

Hi Rob,

there is a solution to solve your problem with a view / select statement, too.

select
top2keys.PID,
top2rows.DATE,
top2rows.OTHER_INFO
from
(
select distinct
PID,
(select top 1 top1.DATE from datatable top1 where base.PID = top1.PID order by top1.DATE desc) as top1date,
(select top 1 top2.DATE from (select top 2 tophelp2.DATE from datatable tophelp2 where base.PID = tophelp2.PID order by tophelp2.DATE desc) top2 order by top2.DATE) as top2date
from
datatable base
) top2keys
left join datatable top2rows
on top2keys.PID = top2rows.PID and (top2keys.top1date = top2rows.DATE or top2keys.top2date = top2rows.DATE)
order by
top2keys.PID,
top2rows.DATE
If you have your PID values in a separate table and pid is a unique key, you can avoid the "select distinct ..." with a "select ..." and "datatable" with the table name where PID is unique to achieve better performance. But this wya should outperform the procedure approach more then 10 times, depending on records in the data table.

Regards,
Tom

|||If you are using SQL Server 2005 you may try the following

WITH T1 as (select RANK() OVER(PARTITION BY PID ORDER BY Date ) AS RNK,* from T)
SELECT PID,Date,otherinfo FROM T1 where RNK<=2