Here, we will take a look at how to use Ranking Functions in SQL Server 2012. Ranking Functions are used to allow the sequential number of the result set or Ranking Functions return a ranking value for each row in a partition. So let's take a look at a practical example of how to use Ranking Functions in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio. There are four new Ranking Functions in SQL Server. Those functions are:
- ROW_NUMBER
- RANK
- DENSE_RANK
- NTILE
Now open a SQL Server Management Studio. Use Start -> All Programs -> Microsoft SQL Server 2012 -> Microsoft SQL Server Management Studio.
Creating a Table in SQL Server
The table looks as in the following:
![Table-in-sqlserver.jpg]()
ROW_NUMBER() Function
The Row_Numaber function is an important function when you do paging in SQL Server. The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
Syntax
ROW_NUMBER ()
OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)
OVER - Specify the order of the rows.
ORDER BY - Provide sort order for the records.
The ROW_NUMBER function enumerates the rows in the sort order defined in the over clause.
Example
SELECT *, ROW_NUMBER() OVER(ORDER BY User_Id) AS Row_Number
FROM userdetail
OUTPUT
![row-number-function-in-sqlserver.jpg]()
Rank() Function
The Rank function produces a rank based on the sorted order when two rows have the same order value. It provides the same rank for the two rows and also increases the same order by clause.
Syntax
RANK ( ) OVER ( [partition_by_clause] order_by_clause )
Example
SELECT *, RANK() OVER(ORDER BY User_Id) AS Rank_Number
FROM userdetail
OUTPUT
![Rank-function-in-sqlserver.jpg]()
In the preceding image two rows have the same order value then provides the same rank for the two rows.
DENSE_RANK() Function
The DENSE_RANK function produces a rank based on the sorted order without any gap. When two rows have the same order value, it provides the same rank for the two rows and also increases the next row value.
Syntax
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Example
SELECT *, DENSE_RANK() OVER(ORDER BY User_Id) AS Dense_Number
FROM userdetail
OUTPUT
![Dense-Rank-Function-in-sqlserver.jpg]()
In the preceding image two rows have the same order value then provides the same rank for the two rows and also increases the next row value. The difference between the Rank() function and the Dense_Rank() function is shown in the output tables of both functions.
NTILE() Function
Divides the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax
NTILE (int_exp) OVER ([<partition_by_clause>]<order_by_clause>)
Example
SELECT *, NTILE(5) OVER (ORDER BY User_Id) AS Ntile_Number
FROM userdetail
OUTPUT
![Ntile-function-in-sqlserver.jpg]()