Return Top 5 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return Top 5

Author

Topic

Dennis Falls
Starting Member

41 Posts

Posted - 2007-09-07 : 13:26:20

I have a table of 60 columns containing 44,000 records. One of the columns is EDFacilityID. There are 104 Distinct EDFacilityID's. I need to return the Top 5 records for each EDFacilityID.

I created a new table and added an ID identity column then inserted my my records with Order by EDFacilityID. But I'm stuck there.

Any quick solutions would be appreciated.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-07 : 13:35:55

Can you post some sample data (not all 60 columns just 3-4 + EDFacilityID will do) and expected output.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2007-09-07 : 13:40:05

In the relational world "Top 5 records" is a meaningless concept without a requirement for ordering.

Jay
to here knows when

Dennis Falls
Starting Member

41 Posts

Posted - 2007-09-07 : 13:53:01

Dinakar,

Here are 4 of the columns

ID EDFacilityID Visit_ID TIME_OF_VISIT
1 101370030 11597712 8/3/2007 6:42:00 PM
2 101370031 11637980 8/4/2007 11:28:00 AM
3 101370032 11667458 8/4/2007 3:59:00 PM
4 101370033 11693846 8/5/2007 4:44:00 AM
5 101370034 11717682 8/5/2007 3:08:00 PM

All I need returned are 5 records from each EDFacilityID. It does not matter which 5 records. I am putting together a sample dataset to send out for analysis and all they require is 5 records from each facility.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-07 : 14:08:28

[code]
Declare @T Table (ID int, EDFacilityID int, Visit_ID int, TIME_OF_VISIT datetime)
Insert into @T
Select 1, 101370030, 11597712, '8/3/2007 6:42:00 PM' union all
Select 2, 101370030, 11597756, '8/3/2007 6:42:00 PM' union all
Select 3, 101370031, 11637980, '8/4/2007 11:28:00 AM' union all
Select 4, 101370032, 11667458, '8/4/2007 3:59:00 PM' union all
Select 5, 101370033, 11693846, '8/5/2007 4:44:00 AM' union all
Select 6, 101370033, 11693868, '8/5/2007 4:44:00 AM' union all
Select 7, 101370034, 11717682, '8/5/2007 3:08:00 PM'

Select T1.*
From @T T1
Join (
Select Min(id) as MId, T.EDFacilityID
from @T T
Group by T.EDFacilityID
) T3 on T1.ID = T3.MId And T1.EDFacilityID = T3.EDFacilityID

[/code]

Replace @T with your table name .

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Dennis Falls
Starting Member

41 Posts

Posted - 2007-09-07 : 15:28:35

Thanks Dinakar,

That works well for returning the top record for each EDFacilityID, now how do I get it to return the top 5 records for each EDFacilityID?

Dennis Falls
Starting Member

41 Posts

Posted - 2007-09-07 : 15:37:18

Nevermind, I figured it out, thanks

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 19:15:51

There is a solution here, in case you would like to compare your solution:

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Kristen

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-07 : 21:30:13