top 15 of one field for each department 1

[dingleberry]

I am embarrassed to ask - I just can't figure this dumb question out. I have a table. In that table I have Store, Department, UPC, Description, Retail $. This table has like 15K records in it.
I am trying to write a query that will return the top 5 (sorted descending on Retail $) for each department.

Right now, when I run the query, because it's pulling the top 5 overall, I'm just getting 15 records from one department as shown.

Store Dept UPC Description Retail $    
2032 BAKERY 73314720166 BREAD PUMPERNICKLE BLK $98.67
2032 BAKERY 73314723819 SL PIE 8IN APPLE $89.85
2032 BAKERY 73314723943 BREAD CHEEZY GARLIC $55.86
2032 BAKERY 73314723575 BUN HAMBURGER WHEAT $54.91
2032 BAKERY 73314723823 SL PIE 8IN PUMPKN $53.91

I would like this but also I would like the same data for PRODUCE, GROCERY, DAIRY, DELI etc.

The sql suntax appears as follows:

SELECT TOP 5 shrink.Store, shrink.Dept, shrink.UPC, shrink.Description, shrink.[Retail $    ]
FROM shrink
WHERE (((shrink.Store)=2032))
ORDER BY shrink.Dept, shrink.[Retail $    ] DESC;

Can this be done without running a seperate query for each department? Thanks!

 

[dhookom]

This isn't a typical query. I usually use a subquery in the criteria that relies on having a primary key field in the table. Do you have one? What do you want to happen if there are ties?

Also please use Pre and Code tags when posting columns of numbers and code so they can be read more easily.

[pre]
Store Dept UPC Description Retail $
2032 BAKERY 73314720166 BREAD PUMPERNICKLE BLK $98.67
2032 BAKERY 73314723819 SL PIE 8IN APPLE $89.85
2032 BAKERY 73314723943 BREAD CHEEZY GARLIC $55.86
2032 BAKERY 73314723575 BUN HAMBURGER WHEAT $54.91
2032 BAKERY 73314723823 SL PIE 8IN PUMPKN $53.91
[/pre]

SELECT TOP 5 shrink.Store, shrink.Dept, shrink.UPC, shrink.Description, shrink.[Retail $    ]
 FROM shrink
 WHERE (((shrink.Store)=2032))
 ORDER BY shrink.Dept, shrink.[Retail $    ] DESC;

Duane
Hook'D on Access
MS Access MVP

 

[dingleberry]

No Sir - No primary Key. Also, I hadn't considered the tie situation. Perhaps I need to consider a different solution.

 

[dhookom]

This is an example of the 3 most recent orders for each customer in the Northwind sample database:

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID In 
(SELECT TOP 3 OrderID
 FROM Orders O
 WHERE O.CustomerID = Orders.CustomerID
 ORDER BY OrderDate Desc)
ORDER BY Orders.CustomerID, Orders.OrderDate DESC;

Duane
Hook'D on Access
MS Access MVP

 

[dingleberry]

Thanks Duane! I think that'll work nicely.

 

[PHV]

What about this ?

SELECT A.Store, A.Dept, A.UPC, A.Description, A.[Retail $    ]
FROM shrink A
INNER JOIN shrink B ON A.Store=B.Store AND A.Dept=B.Dept AND A.[Retail $    ]<=B.[Retail $    ]
WHERE A.Store=2032
GROUP BY A.Store, A.Dept, A.UPC, A.Description, A.[Retail $    ]
HAVING Count(*)<=5
ORDER BY 1, 2, 5 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886