Microsoft introduced ranking functions in Sql Server 2005, which allows the sequential numbering of the result set. These functions are used in the SELECT query and are made to result in another new column. It displays the rank, an integer value for each row of the result set. There are four new ranking functions that have been added to SQL Server 2005.
New Ranking Functions in SQL Server 2005
- ROW_NUMBER
- RANK
- DENSE_RANK
- NTILE
ROW_NUMBER:
This function returns a sequential value for every row in the results. It will assign value 1 for the first row and increase the number of the subsequent rows.
Syntax:
SELECT ROW_NUMBER() OVER (ORDER BY column-name), columns FROM table-name
OVER – Specify the order of the rows
ORDER BY – Provide sort order for the records
Example:
Consider a table Student with the fields Student ID, Student Name, Mark, School.
SELECT ROW_NUMBER() OVER (ORDER BY Mark DESC) AS RowNumber,StudentID,StudentName, Mark, School FROM Student
In this case the Student with the highest mark, when sorted in descending, receives a row number of 1.
Result
RANK:
The RANK function returns the rank based on the sort order. When two rows have the same order value, it provide same rank for the two rows and also the ranking gets incremented after the same order by clause.
Syntax:
SELECT RANK() OVER ([< partition_by_clause >] < order_by_clause >)
Partition_by_clause - Set of results grouped into partition in which RANK function applied.
Order_by_clause – Set of results order the within the partition
Example 1:
SELECT RANK() OVER (ORDER BY School) AS RANK,StudentID,StudentName, Mark, School FROM Student
Result
In the above example, based on the sort order school, the Rank is given.
The first three rows in the list has same school, those rows are given same Rank, followed by the rank of four for another set of rows because there are three rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.
Example 2:
SELECT RANK() OVER (PARTITION BY School ORDER BY Mark Desc) AS RANK,StudentID,StudentName, Mark, School FROM Student
Result
When using the Partition by school, the rank is assigned consecutively based on the sort order Mark for each set of schools.
DENSE_RANK:
The DENSE_RANK function is very similar to RANK and return rank without any gaps. This function sequentially ranks for each unique order by clause.
Syntax:
SELECT DENSE_RANK() OVER ([< partition_by_clause >] )
Partition_by_clause - Set of reults grouped into partition in which DENSE RANK function applied.
Order_by_clause – Set of results Order the within the partition
Example:
SELECT DENSE_RANK() OVER (ORDER BY School) AS [DENSE RANK],StudentID,StudentName, Mark, School FROM Student
Result
In the above example, based on the sort order school, the Rank is given.
The first three rows in the list have the same school; those rows are given the same Rank, followed by the consecutive rank of 2 for another set of rows. This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.
NTILE:
NTILE () splits the set of rows into the specified number of groups. It equally splits the rows for each group when the number of rows is divisible by number of group. The number is incremented for every additional group. If the number of rows in the table is not divisible by total groups count (integer_expression), then the top groups will have one row greater than the later groups. For example if the total number of rows is 6 and the number of groups is 4, the first two groups will have 2 rows and the two remaining groups will have 1 row each
Syntax:
SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] )
(integer_expression) - The number of groups into which each partition must be divided.
Example 1:
SELECT NTILE(2) OVER (ORDER BY School) AS [NTILE2],StudentID,StudentName, Mark, School FROM Student
SELECT NTILE(3) OVER (ORDER BY School) AS [NTILE3],StudentID,StudentName, Mark, School FROM Student
Result
In the above example, NTILE() function divides the rows into groups. The total number of rows is divided by the integer_expression specified.
Example 2: (Using the Partition by Clause)
When using the PARTITION BY Clause, the NTILE() function divides the rows first by the column specified in the PARTITION BY.
SELECT NTILE(2) OVER (PARTITION BY School ORDER BY School) AS [NTILE2],StudentID,StudentName, Mark, School FROM Student
SELECT NTILE(3) OVER (PARTITION BY School ORDER BY School) AS [NTILE3],StudentID,StudentName, Mark, School FROM Student
Result
In the above examples NTILE (2), groups the rows first by school and then divides by the integer_expression 2.
Example
The following example shows the four ranking functions used in the same query.
SELECT StudentID,StudentName,Mark,School
,ROW_NUMBER() OVER (ORDER BY School) AS 'Row Number'
,RANK() OVER (ORDER BY School) AS 'Rank'
,DENSE_RANK() OVER (ORDER BY School) AS 'Dense Rank'
,NTILE(4) OVER (ORDER BY School) AS 'NTile'
FROM Student
Result
