Friday, May 11, 2012 7:33 PM
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.
Friday, May 11, 2012 7:50 PM
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
- Marked As Answer by Yoyo JiangMicrosoft Contingent Staff, Moderator Monday, May 14, 2012 7:47 AM
Friday, May 11, 2012 7:58 PMPerfect! Thank you.