Ranking Data In SQL Server

Facebooktwitterredditpinterestlinkedinmail

When it comes to ranking data in SQL Server, as of SQL Server 2005 they have built this in to the database. In 2005 the SQL Server team introduced the RANK function. The RANK function allows you to assign a number to each record in a result set based something in the data.

Let’s say that you wanted to apply a rank based on the weight of animals in your zoo (from heaviest to lightest). Elephant would get a number of 1 (because it’s the heaviest) and a tiny turtle would get the number 100 (because it’s the lightest). The number returned would start at 1 and grow for every record. This is done using this awesome RANK function.

The RANK function also has a partition feature that allows you to group the data. This allows the ranking number to reset for every group. Using our example above, we could extend it to show us the heaviest to lightest animals based on the animal type. So there would be a #1 ranking for the mammal, amphibian, bird, and reptile animal types.

 
Duplicate Ranks
Because the ranking number is incremented in the order specified by the ORDER BY clause, what happens if there are duplicates? In our example above, what if there are 2 animals with the same weight? If this occurs, the same ranking number will be applied to each of the duplicate records. So if there were 2 animals with the same weight, they would get the same exact ranking number.

 
How To Use RANK
The RANK function has 2 parameters. You must always supply the ORDER BY. The PARTITION BY is optional.

    PARTITION BY – This is what you would like SQL Server to group your rankings by. In the above example, if we want the ranking number to reset for every different animal types, then we would specify that here.
    ORDER BY – The ORDER BY is the order that you want your ranking number to be generated in your result set. This is a required parameter. In the example above, we want it from heaviest to lightest. We would specify the column name here that holds the animal weight.

Here is some sample code and the output to show each of the above scenarios.

In the above example, you can see that we are ranking each record by the weight of the animal from heaviest to lightest. This is done by passing the ORDER BY parameter to the function.

AnimalID AnimalName AnimalWeight AnimalWeightRank
168 Camel 1200 1
162 Carabao 500 2
163 Cattle 400 3
166 Alpaca 350 4
182 Deer 300 5
187 Turkey 30 6
186 Goose 15 7
185 Duck 10 8
171 Cat 10 8
188 Quail 10 8
189 Pigeon 5 11
184 Chicken 5 11

 
 

In the above example, you can see that we are ranking each record by the weight of the animal from heaviest to lightest. We take this a bit further by grouping these by the AnimalType. This is done by passing the PARTITION BY parameter to the function call.

AnimalID AnimalName AnimalType AnimalWeight AnimalWeightRank
187 Turkey Bird 30 1
186 Goose Bird 15 2
185 Duck Bird 10 3
188 Quail Bird 10 3
189 Pigeon Bird 5 5
184 Chicken Bird 5 5
168 Camel Mammal 1200 1
162 Carabao Mammal 500 2
163 Cattle Mammal 400 3
166 Alpaca Mammal 350 4
182 Deer Mammal 300 5
171 Cat Mammal 10 6

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms176102.aspx

Leave a Comment

CommentLuv badge