Top Clause with GROUP BY clause?

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

Hi,

I have a simple table with only 3 fields

Product Category USD_sales

I am looking to get the 5 products that are the most sold from each category.
Can I do that from 1 query or do I need 5?
Many thanks,
Dan
 
If you build a simple query with the query wizard, you can right click in the
area where the tables go and set top values to top 5.
 
One query would probably look like the following:

SELECT Product, Category, USD_Sales
FROM YourTable
WHERE USD_SALES in
(SELECT TOP 5 Temp.USD_Sales
FROM YourTable as Temp
WHERE Temp.Category = YourTable.Category
ORDER BY Temp.USD_Sales DESC)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top