Ranking Functions – ROW_NUMBER,RANK,DENSE_RANK,NTILE

SQL Server 2005 introduced four new ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions allow you to analyze data and provide ranking values to result rows of a query. For example, you might use these ranking functions for assigning sequential integer row IDs to result rows or for presentation, paging, or scoring purposes.

All four ranking functions follow a similar syntax pattern:

function_name() OVER([PARTITION BY partition_by_list] ORDER BY order_by_list)

The basic syntax follows.

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)
RANK() OVER ([<partition_by_clause>] <order_by_clause>)
DENSE_RANK() OVER([<partition_by_clause>]<order_by_clause>)
NTILE(integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)

Ranking functions are a subset of the built in functions in SQL Server. They are used to provide a rank of one kind or another to a set of rows in a partition. The partition can be the full result set, if there is no partition. Otherwise, the partitioning of the result set is defined using the partition clause of the OVER clause. The order of the ranking within the partition is defined by the order clause of OVER. Order is required even though partition is optional.

ROW_NUMBER: ROW-NUMBER 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

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 >)

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

In the above example, based on the sort order Employee Name, the Rank is given.
The first two rows in the list has same Employee Name, those rows are given same Rank, followed by the rank of next for another set of rows because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

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 >] <order_by_clause>)</order_by_clause>

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

 

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 >] <order_by_clause>) </order_by_clause>

SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] )

(integer_expression) – The number of groups into which each partition must be divided.