Return Top 3 Values Within Each Group

Question

I'm looking to create a query that returns the top 3 sizes for each location / type group in MS Access.

RecordID, Location, Type, and Size

1,1,1,20
2,1,2,30
3,1,2,43
4,1,2,23
5,1,2,38
6,1,2,3
7,2,1,12
8,2,1,22
9,2,1,25
10,2,1,43

Result:

1,1,1,20
2,1,2,30
3,1,2,43
5,1,2,38
8,2,1,22
9,2,1,25
10,2,1,43

Is this even possible in MS Access?  Can anyone help?

Answer 1

Hi,

It should be possible. You could try something like:

SELECT SQ.RecordID, SQ.Location, SQ.[Type], SQ.Size
FROM TableName AS T1
INNER JOIN (SELECT TOP 3 T2.RecordID, T2.Location, T2.[Type], T2.Size
  FROM TableName AS T2
  WHERE T2.Location=T1.Location
  AND T2.[Type]=T1.[Type]
  ORDER BY T2.Location, T2.[Type], T2.Size DESC) AS SQ
ON T1.Location=SQ.Location
AND T1.[Type]=SQ.[Type]
AND T1.Size=SQ.Size
GROUP BY T1.Location, T1.[Type]

(untested)

Hope it helps...

Answer 2

Try this:

SELECT T1.RecordID, T1.Location, T1.Type, T1.Size
FROM YourTable AS T1 INNER JOIN YourTable AS T2
ON T1.Location = T2.Location
    AND T1.Type = T2.Type
    AND T2.Size >= T1.Size
GROUP BY T1.RecordID, T1.Location, T1.Type,  T1.Size
HAVING COUNT(*) <=3
ORDER BY T1.Location, T1.Type, T1.Size DESC;

---

Ken Sheridan, Stafford, England

Answer 3

theDBguy,

Thanks for the help but I'm getting an error saying "You tried to execute a query that does not include the specified expression 'RecordID' as part of an aggregate function."  I also get the same error for the 'Size' field too.  I'm sorry this is above my head.  Any suggestions?

Thanks!

Answer 4

Ken,

Thanks for the SQL statement.  I didn't see it before.  It worked like a charm!

Answer 5

Hi,

Sorry it didn't work. I like Ken's solution too. Good luck with your project.