RANKING FUNCTIONS IN SQL

                                                          RANKING FUNCTIONS


What are Ranking Functions?

Ranking functions are a subset of the built in functions in SQL server which are used to provide a rank of one kind or another to set of rows in a partition. The partition can be a full result set, if there is no partition.

There are four types of ranking functions in SQL:

1. ROW_NUMBER

2. RANK

3. DENSE_RANK

4. NTILE

Before learning about the use of the above four ranking functions lets take an example and then see what happens when we use the above functions.

EXAMPLE :

In my example I created a table called Results with the column names Name, Subjects and Marks.

So, now lets see what happens when you use the four functions on the above table in detail.

1. ROW_NUMBER : 

This function will return a row number for each record in the result set.We can further change the resulting row number to reset the row number based on some values in result set.

SYNTAX :

ROW_NUMBER ()  OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

OVER - Specify the order of the rows.

PARTITION BY - This clause is an optional part of the Row_Number function. If you  don't use this clause then all the records of the result set are considered as the single record group or single partition and then the ranking function is applied.

When you specify a column or set of columns with PARTITION BY clause then it will divide the result set into record partitions and then finally ranking functions are applied to each record partition separately and the rank will restart from 1 for each record partition separately.

ORDER BY - This is the specified order of the row number that you would like. If you wanted the order of the row number to increment by an employee name (alphabetically), you do that here.

Example :

ROW_NUMBER function without using Partition clause:

select Name,Subject,Marks,

ROW_NUMBER() over(order by Name) as RowNumber

from Results

ROW_NUMBER function with using Partition clause:

select Name,Subject,Marks,

ROW_NUMBER() over(PARTITION BY Subject order by Name) as RowNumber

from Results

If you see the above result we applied Partition By on Subject column and based on that it divided into three partitions and then ranking function is applied starting from 1 for each record partition.

2. RANK :

Rank function provides consecutive numbering except in case of tie. It means whenever there is a tie between the rows then the ties are assigned the same rank with the next ranking skipped. So, if you have 2 items at rank 1 then the next row is ranked 3.

SYNTAX :

RANK  ()  OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

Example :

RANK() without Partition By clause

select Name,Subject,Marks,

RANK() over(order by Name) as RowNumber

from Results

RANK function with Partition By clause

select Name,Subject,Marks,

RANK() over(partition by Subject order by Name) as Rank

from Results

3. DENSE RANK :

Dense Rank function provides consecutive numbering even in case of ties. It means whenever there is a tie between the rows then the ties are assigned the same rank without the next ranking skipped. So, if you have 2 items at rank 1 then the next row is ranked 2 in this case.

SYNTAX :

DENSE_RANK  ()  OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

Example :

DENSE_RANK function without partition

select Name,Subject,Marks,

DENSE_RANK() over(order by Subject) as DenseRank

from Results

DENSE_RANK function with partition

select Name,Subject,Marks,

DENSE_RANK() over(partition by Subject order by Name) as DenseRank

from Results

4. NTILE :

This function distributes the result set into specified number of ordered partitions.

For each row in result set NTILE() will returns a group number to which the row is associated.

This is very useful while distributing the result set into multiple groups in case you need to distribute the results into multiple tables.

SYNTAX :

NTILE (No of groups into which each partition must be divided) OVER ([PARTITION BY value_exp, ... [ n ]] order_by_clause)

Example :

NTILE function without Partition By

select Name,Subject,Marks,

NTILE(2) over(order by Subject) as NTile

from Results

In the above example, we have specified the no of groups as ‘2’ so it will distribute the result set into 2 different groups by distributing the rows. In this case the rows cannot be distributed evenly so it will be divided as 5+4. If the number of rows were 10 then it will be divided as 5+5.

NTILE function with Partition By

select Name,Subject,Marks,

NTILE(3) over(partition by Name order by Subject) as NTile

from Results

In the above example we have specified the no of groups has ‘3’, so it will distribute the result set into 3 different groups by distributing the rows. In this case the rows are first partitioned by Name and then divided into three groups within each name.

9Rows à    Group 1 à Name=Mahesh =3 Rowsà Partition1 = 1 row

                                                                                    Partition2 = 1 row

                                                                                    Partition3 = 1 row

                           Group 2 à Name=Raj =3 Rowsà Partition1 = 1 row

                                                                                    Partition2 = 1 row

                                                                                    Partition3 = 1 row

                         Group 3 à Name=Ravi =3 Rowsà Partition1 = 1 row

                                                                                    Partition2 = 1 row

                                                                                    Partition3 = 1 row