Top Values within multiple groups

D

Don

I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.
 
K

KARL DEWEY

Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];
 
D

Don

Mr. Dewey,

Can you tell me what the [XX] is in the code you wrote. I cannot get this
to work. Thanks.
--
Don Rountree


KARL DEWEY said:
Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];

--
Build a little, test a little.


Don said:
I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.
 
K

KARL DEWEY

Can you tell me what the [XX] is in the code you wrote.
XX is an alias for the table. I use the table twice but differently so it
must have a different name.
This does not tell me a whole lot so as to know how to advise you.
What is the results? What are the errors?
Post the SQL from your query. Post sample data.

--
Build a little, test a little.


Don said:
Mr. Dewey,

Can you tell me what the [XX] is in the code you wrote. I cannot get this
to work. Thanks.
--
Don Rountree


KARL DEWEY said:
Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];

--
Build a little, test a little.


Don said:
I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.
 
D

Don

I apologize for being so thick. If you could make me understand this code,
I'll be able to duplicate its use in the future.

My data is structured as such:

Table = tblGROCERY
Field = Department
Field = Sales
Field = Item Number
Field = Item Description

I'd like to return the top 5 sales results from each Department. If you
could help me once more with the Syntax for my SQL statement, I won't trouble
you again. Thanks.
--
Don Rountree


KARL DEWEY said:
Can you tell me what the [XX] is in the code you wrote.
XX is an alias for the table. I use the table twice but differently so it
must have a different name.
This does not tell me a whole lot so as to know how to advise you.
What is the results? What are the errors?
Post the SQL from your query. Post sample data.

--
Build a little, test a little.


Don said:
Mr. Dewey,

Can you tell me what the [XX] is in the code you wrote. I cannot get this
to work. Thanks.
--
Don Rountree


KARL DEWEY said:
Try this --
SELECT [Department], (SELECT TOP 5 [Sales] FROM [YourTable] AS [XX] WHERE
[XX].[Department] = [YourTable].[Department] ORDER BY [Sales] DESC) AS
Top_Sales
FROM [YourTable]
ORDER BY [Department];

--
Build a little, test a little.


:

I'm not sure this can be done, but I hope so. I have a query with over 6000
records and within that query I have a field that is titled Department. The
query contains records from 300 departments. Here is what I'd like to do:
I'd like to return the top 5 records for sales, from each department.

I know how to get the top record from each, but not the top 5. I know how
to get the top 5 from my query, but not from each department. Can someone
show this road-weary idiot how to do this? Thanks.
 
J

John Spencer

SELECT Department, Sales, [Item Number], [Item Description]
FROM tblGrocery
WHERE Sales in
(SELECT TOP 5 Sales
FROM tblGrocery as TEMP
WHERE Temp.Department = tblGrocery.Department
ORDER BY Sales DESC)
ORDER BY Department, Sales DESC

This query will show any ties for fifth place.

Open a anew query and paste the query in the SQL view.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top