I need help with top values

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I have a query that includes: Year column formatted as "yyyy", month column
formatted as "mm", and total sales per month. I am trying to get the top 5
months for every year. Is there a way to do that. Access provides only to
show the top over all and this is not what I want.
Thanks
Al
 
Try this --
SELECT Q.Year, Q.Month, Q.[Total Sales], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Year] = Q.[Year]
AND Q1.[Total Sales] > Q.[Total Sales])+1 AS Rank
FROM Product AS Q
WHERE ((((SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Year] = Q.[Year]
AND Q1.[Total Sales] > Q.[Total Sales])+1)<=5))
ORDER BY Q.Year, Q.[Total Sales] DESC;
 
Back
Top