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.
- Include row numbers in your SQL SELECT results
- Partition your SQL SELECT results into groups
- 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.