Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Limit query to Top N values within each group

Posted on 2014-09-30
Medium Priority
163 Views
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
Question by:Dale Logan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 25

Expert Comment

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)));
``````
0

LVL 40

Accepted Solution

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) ;
``````
0

LVL 31

Expert Comment

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:
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
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)
``````
Output will show all records of equal f1 value if that value lies within the top N records.
0

Author Closing Comment

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

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…
Suggested Courses
Course of the Month6 days, 5 hours left to enroll