Solved

Query for top 5 within every group

Posted on 2009-04-02
6
392 Views
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?
0
Comment
Question by:dlogan7
  • 3
6 Comments
 
LVL 3

Expert Comment

by:sreindl04
ID: 24054587
which database system you are working with?
0
 

Author Comment

by:dlogan7
ID: 24054618
I'm sorry. I thought I posted this to the Access section. I am using Access 2007.
0
 
LVL 3

Accepted Solution

by:
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

ORDER BY 3 DESC

UNION

SELECT TOP 3 Region, Product, AvgQuantity

FROM Tabelle1

WHERE region = 2

ORDER BY 3 DESC

Open in new window

0
 
LVL 12

Assisted Solution

by:koutny
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
 

WHERE 

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

0
 
LVL 3

Expert Comment

by:sreindl04
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now