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 DescWhich 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 DescSortName 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.