Top 50 in each group

  • Thread starter Thread starter Trisha
  • Start date Start date
T

Trisha

I have a query where I need the top 50 sku's in each
department. I have in the SQL statement "Select Top 50
dbo_" but it only returns the first top 50 of the first
department. We have about 10 departments and I need the
top 50 of each. How do I do that?

Thanks,
Trisha
 
Trisha,

One way to do this is by means of a union query. Start by making a query to
selest the top 50 for department A, and form the query design view go to SQL
view. You should see a text that looks like:

SELECT TOP 50 TableName.Field1, TableName.Field1...
FROM TableName
WHERE (((TableName.Department) = "A")
WITH OWNERACCESS OPTION;

This is a text editor screen; put your cursor at the beginning of the last
line and push it down by hitting enter a few times. Then copy the precious
lines, paste them as many times as your departments minus 1 (you already
have a copy), change the department code in each copy and precede the first
line with the word UNION. You should get something that looks like:

SELECT TOP 50 TableName.Field1, TableName.Field1...
FROM TableName
WHERE (((TableName.Department) = "A")

UNION SELECT TOP 50 TableName.Field1, TableName.Field1...
FROM TableName
WHERE (((TableName.Department) = "B")

UNION SELECT TOP 50 TableName.Field1, TableName.Field1...
FROM TableName
WHERE (((TableName.Department) = "C")
..
..
..
WITH OWNERACCESS OPTION;

Save it and you're done. Open it to see what you get.

This assumes that the number and codes for your departments do not change
often, so you can change the SQL statement once in a blue moon if need be.
If this is not the case, it would take a bit of VB code to redefine the
query definition dynamically.

Maybe someone else might come up with a better suggestion? Let's wait and
see!

HTH,
Nikos
 
In Northwind, this returns the 3 most recent orders for each USA customer.

SELECT T1.*
FROM Orders AS T1
WHERE (((T1.OrderID) In (SELECT TOP 3 T2.OrderID
FROM Orders AS T2
WHERE T2.CustomerID = T1.CustomerID AND T2.ShipCountry='USA'
ORDER BY T2.OrderDate DESC)) AND ((T1.ShipCountry)='USA'))
ORDER BY T1.CustomerID, T1.OrderDate DESC;

Use the same style query to solve your problem.
 
Back
Top