Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Limit query to Top N values within each group

Posted on 2014-09-30
4
Medium Priority
?
167 Views
Last Modified: 2014-10-09
I've done this before but it's been a really long time. I've searched the site and can't seem to find the perfect answer.

I've got a subquery (Query2) having 3 fields: AreaID, Product, and Acres. I need another query to give me from Query2 the Top 3 Products within each AreaID based on Acres. The best I can come up with is a query that gives me 3 records based on acres and ignores AreaID.

Here's the query as it is right now:

SELECT tblAreas.Area, Query2.Product, Query2.Acres
FROM Query2 INNER JOIN tblAreas ON Query2.AreaID = tblAreas.AreaID
WHERE (((Query2.Acres) In (Select Top 3 [Acres] From [Query2] Where [AreaID]=[tblAreas].[AreaID] Order By [Acres] Desc)));

Thanks, Dale
0
Comment
Question by:Dale Logan
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 40353892
You almost did it. Try this one:
SELECT tblAreas.Area, Query2.Product, Query2.Acres
FROM Query2 INNER JOIN tblAreas ON Query2.AreaID = tblAreas.AreaID
WHERE (((Query2.Product) In (Select Top 3 [Product] From [Query2] Where [AreaID]=[tblAreas].[AreaID] Order By [Acres] Desc)));

Open in new window

0
 
LVL 40

Accepted Solution

by:
als315 earned 2000 total points
ID: 40354170
You can also do it with query2 only:
SELECT q2.AreaID, q2.Acres, q2.Product
FROM Query2 AS q2 Where q2.Acres In (Select Top 3 Acres From Query2 Where Query2.AreaID = q2.AreaID Order By [Acres] Desc) ;

Open in new window

0
 
LVL 31

Expert Comment

by:hnasr
ID: 40354454
If example does not help, try to upload a sample database with the 2 tables including few records, and list the required output.

This is an example:
INPUT:
a(aid, adesc)
1 area1
2 area2
3 area 3
b(aid, bid, f1)
1 1 1
1 2 2
1 3 3
1 4 4
2 1 5
2 1 5
3 1 7

RELATION:
a.aid ----> b.aid

OUTPUT
adesc      bID      f1
area1      4      4
area1      3      3
area1      2      2
area2      2      6
area2      1      5
area3      1      7

Process:
SELECT a.adesc, b.bID, b.f1
FROM a INNER JOIN b ON a.aID = b.aID
WHERE a.aid & b.bid IN (SELECT TOP 3 c.aid & c.bid from b c where c.aid=b.aid order by c.f1 desc)
GROUP BY a.adesc, b.bid, b.f1
ORDER BY a.adesc, b.f1 desc

Open in new window

Output will show all records of equal f1 value if that value lies within the top N records.
0
 

Author Closing Comment

by:Dale Logan
ID: 40370684
The first solution provided was close, but was not returning the correct number of records per group. The second solution provided worked perfectly. Sorry, but I never got to the third solution. Thanks to everyone who offered help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

783 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