SQL: How to order values inside group by
- by Denis Yaremov
Consider the following MS SQL Server table:
ID | X | Y
------+-------+-------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 40
5 | 2 | 500
6 | 3 | 1
7 | 3 | 100
8 | 3 | 10
I need to select the ID of the row that has the maximum value of Y grouped by x, i.e:
ID | X | Y
------+-------+-------
3 | 1 | 3
5 | 2 | 500
7 | 3 | 100
The query will be nested several times so an optimal performance solution is required...
Thank you!