Access Top n in group

I have a table where I need to get the top 25 highest amount items.
I have a query that has all the items and a count column. Having a hard time making a subquery to work.

Ok, Let's say that I have already the following query:

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

Open in new window

PapoteAsked:
Who is Participating?
 
peter57rCommented:
I don't think your question is very clear.  You ask for a different result in the first line to the result you ask for in your example.

I am assuming you want the top 2 items for each category as shown in your example.

Getting the top N per group is a little more involved...put your own tablename in..

Select T.* from tablename as T where T.[inventorycount] in (Select top 2 [inventorycount] from tablename where [category]= T.[category] order by [inventorycount] desc)
order by T.[category], T.[inventorycount] desc
0
 
jnbkzeCommented:
I am not a programmer by any means.
ok, so my idea would be something like this.

search for values, using If statements. So if one number is > than another, then see if that number is higher than another, and anothe and another, untill it is lower than one number. then shift all values in the array of valued 1-25 (of which this value is higher) , down one. starting at the lowest value. drop it off, and make 2=1 3=2 4=3 5=4 .. untill you have shifted them all down, and then enter your current value into the slot where it is lower than another but higher than the others which were shifted down.

I can just see the lines of code for this be pages and pages long, thats why I have no interest in programming :) I`m sur ethere are better methods out there.. perhaps something like a pivot table ? idk, its just a thought. I hope someone else helps you with something easier! :)

good luck!
Edited by WaM 8-14-10
0
 
jennynoverCommented:
in the Query Design window, R.Click in the gray area and select Properties.   There you will see a "Top Values" property.  You put 25 in there.   See also http://www.techonthenet.com/access/queries/top_results.php for detailed instructions.

There you go!
topN.png
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ThomasianCommented:
As jennynover has suggested, you can use TOP to limit the number of records returned. You also need to specify the "Order By" as "InventoryCount DESC" to return the highest amounts.
0
 
Kevin CrossChief Technology OfficerCommented:
Agree with peter57r. That solution should work for say top 5 also but keep in mind that matching inventory counts will come back together like what happens in a TOP WITH TIES query in T-SQL (i.e., you may get 3 or more records even though you ask for top 2) -- this is often desired. If not, then you can try a ranking approach and in a query similar to what peter57r shows you additionally look at item name in relation to other item names (alphabetic sort) so that on matches you chose the item name coming first alphabetically to break tie and assign lower rank ...

Analytical SQL : Where do you rank? - http:A_1555.html

Start with peter57r's approach though and as you need to expand it and take consideration of other factors I mentioned, then please have a read and hopefully it will serve you as well as it does me. :)
0
 
PapoteAuthor Commented:
peter57r: I get a data type mistmatch error.

The code below works to accomplish what I want, but I also need to group by year and location the 2 topmost categories.
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(*) DES

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
That is pretty much what the ranking solution is by the way. As such you may want to read the article as it may help you with adding in additional ranking criteria doing it the way shown versus in the outer query. There is not a year nor a location in the data you showed, so not sure how we may be able to help with that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.