sql - Access top n in group

Question

I have a table where I need to get the top n highest amount items for each Category.

Category Item  InventoryCount
-------  ----- ------------- 
Beverage  milk    3  
Beverage  water   2 
Beverage  beer    9 
Utensil   fork    7 
Utensil   spoon   2 
Utensil   knife   1 
Utensil   spork   4 

My desired output is the highest Inventory of the topmost 2 Categories.

Category Item  InventoryCount
-------  ----- ------------- 
Beverage  beer   9 
Beverage  milk   3 
Utensil   fork   7 
Utensil  spork   4 

Answer 1

This should work for you. If it doesn't satisfy your requirements, post back what you need. Your original desire was to have 25, so you'd simply modify the last clause to be HAVING COUNT(*) <= 25

SELECT  a.item, 
        a.category, 
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, 
          a.item, 
          a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.category, COUNT(*) DESC

If you wanted to select more columns from the table, simply add them to the SELECT and `GROUP BY' clauses.

Only when you want to expand the "TOP n for each Category, foo, bar", then you would add those columns to the INNER JOIN clause as well.

--show the top 2 items for each category and year.
SELECT  a.item, 
        a.category, 
        a.year,
        a.inventorycount, 
        COUNT(*) AS ranknumber
FROM inv AS a 
INNER JOIN inv AS b 
     ON (a.category = b.category) 
     AND (a.year = b.year) 
     AND (a.inventorycount <= b.inventorycount)
GROUP BY  a.category, a.item, a.year, a.inventorycount
HAVING COUNT(*) <= 2
ORDER BY a.year, a.category, COUNT(*) DESC