none
Return Top 3 Values Within Each Group RRS feed

  • 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?

    Wednesday, January 4, 2017 1:29 PM

Answers

  • 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


    • Edited by Ken Sheridan Wednesday, January 4, 2017 5:47 PM
    • Marked as answer by GMcElroy20 Wednesday, January 4, 2017 6:21 PM
    Wednesday, January 4, 2017 5:43 PM

All replies

  • 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...


    • Edited by .theDBguy Wednesday, January 4, 2017 6:10 PM
    Wednesday, January 4, 2017 2:22 PM
  • 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


    • Edited by Ken Sheridan Wednesday, January 4, 2017 5:47 PM
    • Marked as answer by GMcElroy20 Wednesday, January 4, 2017 6:21 PM
    Wednesday, January 4, 2017 5:43 PM
  • 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!

    Wednesday, January 4, 2017 6:13 PM
  • Ken,

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

    Wednesday, January 4, 2017 6:20 PM
  • Hi,

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

    Wednesday, January 4, 2017 6:25 PM