SQL 2005+SQL Server Ranking Functions
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 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
Engineers4 August 2009