Freitag, 11. Mai 2012 19:33
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.
Freitag, 11. Mai 2012 19:50
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
- Als Antwort markiert Yoyo JiangMicrosoft Contingent Staff, Moderator Montag, 14. Mai 2012 07:47
Freitag, 11. Mai 2012 19:58Perfect! Thank you.