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:
ROW_NUMBER() Function in SQL Server
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.
Row_Number() Example
- SELECT *, ROW_NUMBER() OVER(ORDER BY User_Id) AS Row_Number
- FROM userdetail
OUTPUT
Rank() Function in SQL Server
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 )
Rank() Example
- SELECT *, RANK() OVER(ORDER BY User_Id) AS Rank_Number
- FROM userdetail
OUTPUT
In the preceding image two rows have the same order value then provides the same rank for the two rows.
DENSE_RANK() Function in SQL Server
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>)
Dense_Rank() Example
- SELECT *, DENSE_RANK() OVER(ORDER BY User_Id) AS Dense_Number
- FROM userdetail
OUTPUT
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 in SQL Server
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>)
Ntile() Example
- SELECT *, NTILE(5) OVER (ORDER BY User_Id) AS Ntile_Number
- FROM userdetail
OUTPUT