There are 4 Ranking functions provided by SQL server
1) ROW_NUMBER()
2) RANK ()
3) DENSE_RANK()
4) NTILE()
In this article, I am going to explain these functions with the help of the table "tbl_companysales". Queries for creation and insertion of data into this table is given below:-
Create Table tbl_companysales(id int identity(1,1), employeeid int, employeename nvarchar(150), month_name nvarchar(100), year int,salesamount int)
Insert Into tbl_companysales (employeeid,employeename,month_name,year,salesamount)
Select 1, 'Vivek' ,'April', 2012, 200000
union all
Select 2, 'Ravi', 'April', 2012, 150000
union all
Select 3, 'Uma', 'April', 2012, 150000
union all
Select 4, 'Raman','April', 2012, 140000
union all
Select 1, 'Vivek', 'October', 2012, 300000
union all
Select 2, 'Ravi', 'October', 2012, 200000
union all
Select 3, 'Uma', 'October', 2012, 400000
union all
Select 4, 'Raman','October', 2012,300000
union all
Select 5, 'Monika', 'October', 2012, 300000
union all
Select 1, 'Vivek' , 'November', 2012, 300000
union all
Select 2, 'Ravi', 'November', 2012,200000
union all
Select 3, 'Uma', 'November', 2012, 200000
union all
Select 4, 'Raman', 'November', 2012, 200000
union all
Select 5, 'Monilka', 'November', 2012, 400000
We can use the following query to know the rows contains by this table
select * from tbl_companysales
ROW_NUMBER():- This function gives the sequential number of a row within a partition of a resultset, starting from 1.
Syntax :-
ROW_NUMBER ( ) OVER ( [
Example:- If we want to gives rank to employee within a month, we can use the following query
Query:-
SELECT employeename,month_name ,ROW_NUMBER () OVER (partition by month_name ORDER BY month_name) AS "Row Number" ,salesamount FROM tbl_companysales
Result:-
This query will gives the following result:-
If we see the result, we found that the function ROW_NUMBER() gives every row a number within a partition which is in case is month.
RANK():- This function return the rank of a row within a partition of a result set
Syntax:-
RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )
Example:-Suppose if we want to get the rank of employee according to their sales amount within a month, we can use the following query
Query:-
SELECT employeename,month_name ,RANK() OVER (partition by month_name ORDER BY salesamount) AS Rank ,salesamount FROM tbl_companysales
Result:-
This query will gives the following result:-
If we see that result we found that RANK() function gives each rows a rank based on the sales amount value within a partition (in this month). Also we see that if more than one rows have the same sales amount then it gives same rank to all the rows containing the same sales amount value within a partition and for the next row it gives the next rank with a gap. For example in the above result, Vivek get the rank 4th in month April since it comes on 4th number instead of 3 and two rows in April with employees Ravi and Uma get the same rank.
DENSE_RANK():- This function returns the rank of a row within a partition of a result set similarly to Rank function but it return the rank of a row without any gap.
Syntax:-
DENSE_RANK ( ) OVER ( [
Example:-Suppose if we want to get the dense rank of employee according to their sales amount within a month, we can use the following query
Query:-
SELECT employeename,month_name ,DENSE_RANK() OVER (partition by month_name ORDER BY salesamount) AS "Dense Rank" ,salesamount FROM tbl_companysales
Result:-
This query will gives the following result:-
If we see the result , we found that Dense_Rank() function gives Rank without any gap as it gives 3rd rank to employee Vivek in month April.
NTILE():- This function distributes the rows in an ordered partition into a specified number of groups. For each row, NTILE() will returns the number of the group to which the row belongs.
Syntax:-
NTILE (integer_expression) OVER ( [
Example:-
In the following example, we divides the rows into 4 groups. since the number of rows are not fully divisible by 4 so it creates first two groups of 4 rows and next 2 groups of 3 rows each
Query:-
SELECT employeename,month_name ,NTILE(4) OVER (ORDER BY month_name) AS Quartile ,salesamount FROM tbl_companysales
Result:-
This query will gives the following result:-
If we see the result, we found that the Ntile() function divides the rows into 4 groups. Employees in the first group are given the rank 1 since they belong to first group. similarly are employees are given the rank according to the number of the group to which they belong.
Summary :- In this article , I tried to explain the concepts of Ranking functions in SQL Server with the help of examples. We mostly used the functions Row_number() and Rank(). This function is also used in deleting the duplicate records in a table. I am waiting for your valuable comments (feedback) on this article. If you have any query regarding this, you can send me a mail at askvivekjohari@gmail.com.
Keep reading and keep visiting my blog 🙂
