Ranking Functions in SQL Server 2012

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:

1. ROW_NUMBER
2. RANK
3. DENSE_RANK
4. 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

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

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

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

 

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