Solved

Limit query to Top N values within each group

Posted on 2014-09-30
4
154 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:dlogan7
4 Comments
 
LVL 24

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 39

Accepted Solution

by:
als315 earned 500 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 30

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DSum for Access 6 47
Access 2010 db.Execute using dbFailOnError and dbSeeChanges 10 49
Search Form not Querying 2 11
tabctrl with page click event 9 12
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

867 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

21 Experts available now in Live!

Get 1:1 Help Now