Given the following table and info:
ID Value Order
1 A 10
2 A 8
2 C 10
2 D 9
3 E 8
3 F 10
4 A 9
4 B 10
4 C 8
4 E 7
I want the top 2 Values in descending order for each ID. In other words, the result set should look like this:
ID Value Order
1 A 10
2 C 10
2 D 9
3 F 10
3 E 8
4 B 10
4 A 9
So it would be the equivalent of:
SELECT DISTINCT(ID), TOP 2(Value)
FROM Table
GROUP BY ID
ORDER BY Order DESC
but I couldn't find an aggregate function that would do this for me, and I couldn't figure out a way to use LIMIT.
Any ideas? I hope this is clear enough.
Start Free Trial
