Query for top 5 within every group

Posted on 2009-04-02
Last Modified: 2013-11-28
I have a totals query with 3 fields: Region, Product, and AverageQty. I want a query that will only return the top 5 AverageQty within each region.

I can't recall ever using the drop down in a query to return the top 5. When I try it for this query I only get the top 5 records for a query. What else needs to be done to the query?
Question by:Dale Logan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Expert Comment

ID: 24054587
which database system you are working with?

Author Comment

by:Dale Logan
ID: 24054618
I'm sorry. I thought I posted this to the Access section. I am using Access 2007.

Accepted Solution

sreindl04 earned 250 total points
ID: 24054796
I was thinking on hierachies in oracle sqlplus but in Access... Use VBA and SELECT TOP might help...

The only idea I have is using unions if you know the regions in advance (see sql example)
SELECT TOP 3 Region, Product, AvgQuantity 
FROM Tabelle1
WHERE region = 1
SELECT TOP 3 Region, Product, AvgQuantity
FROM Tabelle1
WHERE region = 2

Open in new window

LVL 12

Assisted Solution

koutny earned 250 total points
ID: 24058168
I don't know whether a solution is still required but the query might look something like the code snippet below.
The only problem with that query would be that in case there would be several products with the same averages then the query would show them all - it basically shows all products for each region with averages in the top 5.

I don't know where you are using this query but if you are trying to produce a report then there are other ways of achieving this (subreports).
SELECT R.RegionID, R.RegionName, RA.Product, RA.AverageQty
FROM Regions R INNER JOIN queryRegionAverages RA 
ON R.RegionID = RA.RegionID
RA.AverageQty in (SELECT TOP 5 AverageQty FROM queryRegionAverages RA2 WHERE RA.RegionID = RA2.RegionID ORDER BY RA2.queryRegionAverages DESC)
ORDER BY R.RegionID, RA.AverageQty DESC

Open in new window


Expert Comment

ID: 24068836
The point with the "same" averages prevented me from providing an sql with subselects. You might work with subsubselects but I do not know if access supports this kind of queries and the performance impact might be high. Access does not scale very well with subselects especially when working on shares.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question