Top X customers

F

franky

Hello,

I have a query (qryCustSales) with the following fields; customer, salesman,
and SumOfPrice. How do I get the top 15 customers for EACH salesman based on
the SumOfPrice?

Thanks in advance!
 
O

Ofer

Try this

SELECT M1.customer, M1.salesman, M1.SumOfPrice
FROM qryCustSales AS M1
WHERE M1.customer In (SELECT Top 15 M2.customer
FROM qryCustSales as M2
WHERE M2.salesman =M1.salesman
ORDER BY M2.SumOfPrice Desc)
 
T

Tom Ellison

Dear Franky:

You can rank the customers by salesman, giving each customer a value
starting with 0 for the best customer and counting upward.

SELECT customer, salesman, SumOfPrice,
(SELECT COUNT(*) FROM qryCustSales Q2
WHERE Q2.salesman = Q1.salesman
AND Q2.SumOfPrice > Q1.SumOfPrice) AS Rank
FROM qryCustSales Q1

Save the above query as, say, MyQuery, then:

SELECT * FROM MyQuery WHERE Rank < 15

This then returns the customers with Ranks from 0 to 14.

Note that, if there is a 2 way tie for 15th place (Rank = 14) then BOTH
customers will appear. That seems very natural to me. We could add a
"tie-breaker" (such as using the customer's name as a secondary ranking
value) to arbitrarily eliminate one of the customers so you never have more
than 15.

Please let me know how this worked for you.

Tom Ellison
 
Top