filter for top 10

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

c - 11 Nov 2005 16:27 GMT

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

Reply to this Message

John Spencer - 11 Nov 2005 17:15 GMT

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.

>I have a query with the following 3 fields:
>
[quoted text clipped - 15 lines]
> Thanks,
> c

Reply to this Message