Rank your SQL SELECT results

Luke Canvin

The third of a series of posts introducing handy functions built into SQL Server (2005 and above) to help you include interesting metadata about your SELECT statement's results.

  1. Include row numbers in your SQL SELECT results
  2. Partition your SQL SELECT results into groups
  3. Rank your SQL SELECT results

Rank() and Dense_Rank()

If you'd like to rank your SQL SELECT result set by one or more factors then you can use Rank() or Dense_Rank(), which are best demonstrated with an example:

Select SortName,
       TestScore,
       Row_Number() Over (Order By TestScore Desc) As Row_Number,
       Rank() Over (Order By TestScore Desc) As Rank,
       Dense_Rank() Over (Order By TestScore Desc) As Dense_Rank
From   T_Tests
Where  Surname = 'Lesseps'
Order By TestScore Desc

Which gives us:

SortName            TestScore  Row_Number   Rank    Dense_Rank
------------------  ---------  ----------   ------  ----------
Lesseps, Abby       100        1            1       1
Lesseps, Aaliyah    98         2            2       2
Lesseps, Abigail    98         3            2       2
Lesseps, Aaliyah    95         4            4       3
Lesseps, Abby       90         5            5       4
Lesseps, Aaralyn    89         6            6       5
Lesseps, Abrianna   88         7            7       6
Lesseps, Addison    88         8            7       6
Lesseps, Aaron      87         9            9       7
Lesseps, Addison    87         10           9       7
Lesseps, Aaliyah    85         11           11      8
Lesseps, Abby       85         12           11      8
Lesseps, Abby       84         13           13      9
Lesseps, Abigail    83         14           14      10
Lesseps, Aaliyah    80         15           15      11
Lesseps, Addison    76         16           16      12
Lesseps, Adam       75         17           17      13
Lesseps, Aaralyn    73         18           18      14
Lesseps, Abrianna   70         19           19      15
Lesseps, Aaliyah    68         20           20      16
Lesseps, Addison    68         21           20      16

As you can see, Rank gives us a straight ranking and where two results are tied, they receive the same rank and then the next result picks up the next appropriate rank. In our example results 2 and 3 have the same test score and so both are given the rank of 2; result 4 then picks up from there with a rank of 4. So using Rank will not guarantee consecutive ranking numbers.

Dense_Rank is slightly different, it does guarantee consecutive ranking numbers, so in this case results 2 and 3 have a rank of 2, and then result 4 has a rank of 3.

Ranking within partitions

We looked at how to partition your result set back in the second article in this series - you can also perform rankings within partitions. Take this example:

Select SortName,
       TestScore
       Rank() Over (Partition By Forename Order By TestScore Desc) As Rank
From   T_Tests
Where  Surname = 'Lesseps'
Order By SortName Asc, TestScore Desc
SortName            TestScore  Rank
------------------  ---------  ----
Lesseps, Aaliyah    98         1
Lesseps, Aaliyah    95         2
Lesseps, Aaliyah    85         3
Lesseps, Aaliyah    80         4
Lesseps, Aaliyah    68         5
Lesseps, Aaralyn    89         1
Lesseps, Aaralyn    73         2
Lesseps, Brian      100        1
Lesseps, Brian      93         2
Lesseps, Brian      93         2
Lesseps, Brian      85         4

So here each person's score is ranked partitioned by their name, so the ranking begins again for each person.

Leave a Reply