Ranking Top 5

[JazzEars]

I need to find the top 5 customers for each salesposition and create a query or table that shows the Rank, Salesposition, Customer and Revenue where revenue is >0

My table has the following fields:
ID (unique)
Customer
SalesPosition
Revenue

So I would like to see something like this:
Rank SalesPosition Customer Revenue
1 P001 Acme $10000
2 P001 World $8000
3 P001 AB Smith $5000
4 P001 Orange Dist $2000
5 P001 LemonsRUs $500
1 P002 OnionsCry $15000
2 P002 Apples2Go $12000
(In this case P002 only has 2 customers)
1 P003 CC Ryder $35000
2...

Any help would be greatly appreciated

 

[PHV]

SELECT * FROM (
SELECT (SELECT Count(*) FROM yourTable WHERE SalesPosition=A.SalesPosition AND Revenue>=A.Revenue) AS Rank, SalesPosition, Customer, Revenue
FROM tblSpongle AS A WHERE Revenue > 0
) AS R
WHERE Rank <= 5
ORDER BY 2, 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886