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

Answer 1

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

Answer 2

Perfect! Thank you.

---

Harrisnl