filter for top 10

[c]

I have a query with the following 3 fields:

Customer Item # Qty Ordered

There are 5 customers, and up to 100 items. I want to filter the top 10 for
each customer. Currently the query is sorted by customer name, then
descending by Qty Ordered. Looks like:

Customer Item # Qty Ordered
Bob Item A 1000
Bob Item B 700
Bob Item C 500

If I group by Max under Qty Ordered I only get the highest, I need the 10
highest. Is there any way to do this?

Thanks,
c

 

[John Spencer]

Yes there is a way. You need to use a correlated subquery.

SELECT Customer, [Item #], [qty Ordered]
FROM SomeQueryOrTable
WHERE [qty Ordered] in
(SELECT TOP 10 tmp.[Qty Ordered]
FROM SomeQueryOrTable as Tmp
WHERE tmp.Customer = SomeQueryOrTable.Customer
ORDER BY tmp.[Qty Ordered] DESC, Tmp.[Item #])

That should give you 10 items and quantities for each customer in the
query/table. If you want ties, then drop the Tmp.[Item #] from the ORDER BY
clause in the Sub-query.