TOP 3 date per id

Question

Post reply

TOP 3 date per id

Tri Phan

SSC Veteran

Points: 267
More actions
April 26, 2006 at 5:55 pm

#113967

Hi all,
can you help me how to find 3 latest date per id???
Table
ID               Date
1               1/1/06
1               3/12/06
1               3/13/06
1               4/11/06
1              4/19/06
2              1/1/06
2               3/12/06
2               3/13/06
2               4/11/06
2               4/19/06
3               1/1/06
3               3/12/06
3               3/13/06
3               4/11/06
3              4/19/06
........
I can have a result
ID               Date
1               1/1/06
1               3/12/06
1               3/13/06
2              1/1/06
2               3/12/06
2               3/13/06
3               1/1/06
3               3/12/06
3               3/13/06
.............
THANKS

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Riyaz Ghouse SSC-Addicted Points: 464 More actions · Answer 1

create table #tempo (id int, generated bit)

insert into #tempo (id, generated)

select distinct id, 0 from [YOUR_TABLE]

create table #temp1 (id1 int, date1 datetime)

declare @id int

while exists (select * from #tempo where generated = 0)

begin

select top 1 @id = id from #tempo where generated = 0 order by id asc

insert into #temp1 ([id1], [date1])

select top 3 id, date from [YOUR_TABLE] where id = @id order by date asc

update #tempo set generated = 1 where id = @id

end

select * from #temp1

drop table #temp1

drop table #tempo

---That should be all....hope that helps

Riyaz

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Hi all,

Here's a simpler way...

--data

set dateformat mdy

declare @t table (ID int, Date datetime)

insert @t

select 1, '1/1/06'

union all select 1, '3/12/06'

union all select 1, '3/13/06'

union all select 1, '4/11/06'

union all select 1, '4/19/06'

union all select 2, '1/1/06'

union all select 2, '3/12/06'

union all select 2, '3/13/06'

union all select 2, '4/11/06'

union all select 2, '4/19/06'

union all select 3, '1/1/06'

union all select 3, '3/12/06'

union all select 3, '3/13/06'

union all select 3, '4/11/06'

union all select 3, '4/19/06'

--calculation

select * from @t a where (select count(*) from @t where id = a.id and date <= a.date) <= 3

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

Tim Deardeuff SSC Veteran Points: 237 More actions · Answer 3

Just out of curiosity I tested both examples above. I wanted to see what the difference in performance would be. I ran both against a 20 million+ row table.

RyanRandall's solution took 17:25 and returned 3,770,000 rows.

Grasshopper's temp table solution ran for 2 hours before I killed it. I decided the difference had been well illustrated by then. It had only processed 1584 of the 1,360,000 distinct id's it had identified. Of course, if you were working with much smaller tables, it might not be such a big deal.

Tim

"Thought provoking quote goes here"

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 4

Hi,

Use this Query it may be benificial to you :

Declare @COUNT INT,@str varchar(4000),@ntop int

SELECT @COUNT=COUNT(distinct r_id) FROM tbl_date

set @ntop=@count * 3

print @ntop

set @STR='select * from

(select top ' + convert(varchar,@ntop) + ' r_id,dt from tbl_date group by r_id,dt order by dt desc)a

ORDER BY 1,2 desc'

exec(@str)

Use you table : instead of tbl_date

Regards,

Amit Gupta

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 5

sorry Boss,

You have to change your Query, You have to use acs in place of dsc like

select top ' + convert(varchar,@ntop) + ' r_id,dt from tbl_date group by r_id,dt order by dt desc

select top ' + convert(varchar,@ntop) + ' r_id,dt from tbl_date group by r_id,dt order by dt ASC

Regards,

AMIT GUPTA