Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Query for top 5 within every group

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
Dale Logan
Asked:
Dale Logan
  • 3
2 Solutions
 
sreindl04Commented:
which database system you are working with?
0
 
Dale LoganConsultantAuthor Commented:
I'm sorry. I thought I posted this to the Access section. I am using Access 2007.
0
 
sreindl04Commented:
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
 
koutnyCommented:
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
 
sreindl04Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now