SQL 2005+SQL Server Ranking Functions (2)
The thirty-first part of the SQL Server Programming Fundamentals tutorial describes the use of ranking functions within SQL queries. These functions add a new column to the results of a query, applying a ranking value to every row that is returned.
Using Rank
The Rank function is used with a similar syntax to Row_Number. The results are ranked according to the sort order, with each row being given a number that is one greater than the number of results that appear before it. Where there are two or more items that are equivalent in the sort order they are each given the same rank. For example, if the first two items in the list are equivalent, they are both given a rank of one. The next item in the list will have a rank of three.
The table below shows the rank compared to the row number for a group of salespeople. Each ranking function in the grid is applied with a sort order based upon descending sales value:
| Salesperson | Sales | Row_Number | Rank |
|---|
| Bob | £21,000 | 1 | 1 |
| Jim | £20,000 | 2 | 2 |
| Sam | £20,000 | 3 | 2 |
| Tim | £18,000 | 4 | 4 |
| Tom | £16,000 | 5 | 5 |
| Liz | £16,000 | 6 | 5 |
| Vic | £16,000 | 7 | 5 |
| Mel | £14,000 | 8 | 8 |
To demonstrate, execute the following statement against the JoBS database. This query lists all of the engineers ranked by their hourly charge rate.
SELECT
EngineerId,
EngineerName,
HourlyRate,
rank() OVER (ORDER BY HourlyRate) AS RankNumber
FROM
EngineersUsing Dense_Rank
The Dense_Rank function is very similar to Rank. The key difference is that there are no gaps between the rank values when duplicates are encountered. If the first two values in the sort order are identical, each will be given a value of one. The third value would have a dense rank of two, not three.
| Salesperson | Sales | Row_Number | Rank | Dense_Rank |
|---|
| Bob | £21,000 | 1 | 1 | 1 |
| Jim | £20,000 | 2 | 2 | 2 |
| Sam | £20,000 | 3 | 2 | 2 |
| Tim | £18,000 | 4 | 4 | 3 |
| Tom | £16,000 | 5 | 5 | 4 |
| Liz | £16,000 | 6 | 5 | 4 |
| Vic | £16,000 | 7 | 5 | 4 |
| Mel | £14,000 | 8 | 8 | 5 |
The following query generates the dense rank for the engineers, again based upon hourly rates.
SELECT
EngineerId,
EngineerName,
HourlyRate,
dense_rank() OVER (ORDER BY HourlyRate) AS DenseRank
FROM
EngineersUsing Ntile
The Ntile function splits the results of a query into a number of groups. Each group is then given a number that is applied to every row within the group. The numbering starts with group one and is incremented for every additional group. The syntax for the statement is similar to that of the previous functions with the addition of a parameter to specify the number of groups that should be generated.
Where possible, each group will contain exactly the same number of rows. However, if the number of rows is not divisible by the number of groups, the later groups will be one row smaller than the first group. The table below shows this in the final two columns. The penultimate column divides the results into four equal groups. The final column has three groups so the third group contains two rows compared to the first two groups' three rows.
| Salesperson | Sales | Row_Number | Rank | Dense_Rank | Ntile(4) | Ntile(3) |
|---|
| Bob | £21,000 | 1 | 1 | 1 | 1 | 1 |
| Jim | £20,000 | 2 | 2 | 2 | 1 | 1 |
| Sam | £20,000 | 3 | 2 | 2 | 2 | 1 |
| Tim | £18,000 | 4 | 4 | 3 | 2 | 2 |
| Tom | £16,000 | 5 | 5 | 4 | 3 | 2 |
| Liz | £16,000 | 6 | 5 | 4 | 3 | 2 |
| Vic | £16,000 | 7 | 5 | 4 | 4 | 3 |
| Mel | £14,000 | 8 | 8 | 5 | 4 | 3 |
We can modify the previous SQL statement to use Ntile. In the following example, the engineers are split into four groups:
SELECT
EngineerId,
EngineerName,
HourlyRate,
ntile(4) OVER (ORDER BY HourlyRate) AS GroupNumber
FROM
Engineers
4 August 2009