asked on

Limit query to Top N values within each group

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

chaau

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

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

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.

Dale Logan

ASKER

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.