Search code examples
sqlsql-serversql-server-2014

SQL Server - How to query the set of maximum numbers from a list of numbers from top to bottom

Best way to explain this would be through an example. Let's say I have this simple 2 column table:

Id | Score
1  | 10
2  | 5
3  | 20
4  | 15
5  | 20
6  | 25
7  | 30
8  | 30
9  | 10
10 | 40

The query should return the IDs of each item where the max score changed. So, from the top, 10 would be the top score since item 1 has 10 the first time through but then on item 3 it has a score of 20 so it just had a new max score and this continues until the bottom of the table. So eventually, the query will result to:

1, 3, 6, 7, 10

I tried doing a Cursor and loop through the table but I was wondering if there was a much simple way of doing this.

Thanks

Solution

  • Solution (SQL2012+):

    SELECT v.MaxScore, MIN(v.Id) AS FirstId
FROM (
    SELECT *, MAX(t.Score) OVER(ORDER BY t.Id ASC) AS MaxScore
    FROM @Table AS t
) v
GROUP BY v.MaxScore

    Demo