Solved

Limit query to Top N values within each group

Posted on 2014-09-30
4
157 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

786 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