how do you get the top 5 rows per group?

I basically have a list of employees with 2 different site numbers and and i want the top x number of employees for each site, i just cant figure this one out, in this list ive shown top 2...

NAME   ID      SITENO
Bob      100    001
Fred      4       002
Jack      41     002
Jill        11      001
Sam      23     001
Sarah    24     002
Tina       21     002


NAME   ID      SITENO
Bob      100    001
Jill        11      001
Fred      4       002
Jack      41     002

ClockOn

You can do it with a temporary table and a script:

SELECT * INTO TmpTable FROM EmpTable WHERE SiteNo = '001' ORDER BY Name TOP 5 ;
INSERT INTO TmpTable SELECT * FROM EmpTable WHERE SiteNo = '002' ORDER BY Name TOP 5 ;
SELECT * FROM TmpTable ;


--
Fernando Dias
[Team Elevate]

Hi Fernando

thanks for the reply but i was hoping for something a bit more generic as in most instances I do not know the SiteNo. I was thinking of trying to get a running count by location of how many shifts there are and removing anything > 50 but Ive had no luck getting that running.

I meant employees not shifts, lol...

ClockOn


I think John Hay has posted a solution to this sort of problem. I can't remember which post it was but try searching for his name in these newsgroups.

Roy Lambert [Team Elevate]

Thanks Roy i found it

http://www.elevatesoft.com/forums?action=view&category=dbisam&id=dbisam_sql&msg=13709&start=31&keywords=John%20Hay&searchbody=True&forum=DBISAM_Announce&forum=DBISAM_General&forum=DBISAM_SQL&forum=DBISAM_CS&forum=DBISAM_ODBC&forum=DBISAM_Beta&forum=DBISAM_Linux&forum=DBISAM_Binaries&forum=DBISAM_Suggestions&forum=DBISAM_ThirdParty&forum=DBISAM_Discussion#13709