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.
|
1 2 3 4 5 |
SELECT AnimalID, AnimalName, AnimalWeight, RANK() OVER (ORDER BY AnimalWeight DESC) AS AnimalWeightRank FROM Animal |
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 |
|
1 2 3 4 5 6 |
SELECT AnimalID, AnimalName, AnimalType, AnimalWeight, RANK() OVER (PARTITION BY AnimalType ORDER BY AnimalWeight DESC) AS AnimalWeightRank FROM Animal |
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