Select top records in each group...

[jtrembla]

I am trying to select the top 5 records by employees here in each grouping of Ultimate Duns numbers. Unfortanely the top N function only returns N records. How do I go about doing this? Can it be done with a query? Ex:

ULTIMATE_DUNS EMPLOYEES_HERE
001024314 100
001024314 100
001024314 50
001024314 147
001024314 0
001024314 3
245212181 522
245212181 20
245212181 52
245212181 541
245212181 56
245212181 3

 

[PHV]

One way (SQL code):
SELECT ULTIMATE_DUNS, EMPLOYEES_HERE
FROM yourTable AS A
WHERE EMPLOYEES_HERE In (SELECT TOP 5 EMPLOYEES_HERE
FROM yourTable WHERE ULTIMATE_DUNS=A.ULTIMATE_DUNS ORDER BY 1 DESC)
ORDER BY 1, 2 DESC

Another way (SQL code):
SELECT A.ULTIMATE_DUNS, A.EMPLOYEES_HERE
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ULTIMATE_DUNS=B.ULTIMATE_DUNS AND A.EMPLOYEES_HERE<=B.EMPLOYEES_HERE
GROUP BY A.ULTIMATE_DUNS, A.EMPLOYEES_HERE
HAVING Count(*)<=5
ORDER BY 1, 2 DESC;


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886