none
Access query multiple maxes

    Question

  • I have been asked to find a way to write a query that instead of listing the Max() for a column would list the 3 highest values. This by itself is no problem but they want to get the 3 highest for each value in a second column. So for example, if the column to max had financial data and the column to categorize was departments, they would want the top three amounts for each department in one query. Assuming many departments, this really can't be done as multiple queries.

    Any ideas?


    Harrisnl

    Friday, May 11, 2012 7:33 PM

Answers

  • Let's say you have table MyData, and you want to group on FieldA and return the three highest values from FieldB.

    The SQL for the query could look like this:

    SELECT MyData.FieldA, MyData.FieldB 
    FROM MyData 
    WHERE MyData.FieldB IN 
        (SELECT TOP 3 T.FieldB
        FROM MyData AS T
        WHERE T.FieldA = MyData.FieldA
        ORDER BY T.FieldB DESC)

    Remark: if there is a tie for third place, you may get more than three records per group.

    Regards, Hans Vogelaar

    Friday, May 11, 2012 7:50 PM

All replies

  • Let's say you have table MyData, and you want to group on FieldA and return the three highest values from FieldB.

    The SQL for the query could look like this:

    SELECT MyData.FieldA, MyData.FieldB 
    FROM MyData 
    WHERE MyData.FieldB IN 
        (SELECT TOP 3 T.FieldB
        FROM MyData AS T
        WHERE T.FieldA = MyData.FieldA
        ORDER BY T.FieldB DESC)

    Remark: if there is a tie for third place, you may get more than three records per group.

    Regards, Hans Vogelaar

    Friday, May 11, 2012 7:50 PM
  • Perfect! Thank you.

    Harrisnl

    Friday, May 11, 2012 7:58 PM