RANKING FUNCTIONS in SSMS (ROW_NUMBER, RANK, DENSE_RANK, NTILE)

There are various ranking function exists in SSMS. We will to through one by one.

ROW_NUMBER: This is a kind of unique number generation function in SSMS. It will count the row of table and set the row count by incremental without bothering data value.

For example, suppose there are 4 rows exists in a table, if we use row_number over a column, then we will get 1,2,3,4.

RANK: It returns ranking of data value of mentioned column under the function. Here we cannot get the proper ranking deviation. It detect the highest value of mentioned column and check for same value exists in other row, and based on the row count of same valued row, the second highest valued row will be ranked.

For example, as previous there are 4 rows exists in a table, if the highest value is exist in  3 rows, then this 3 rows rank will be 1,1,1 and second highest row rank will be 4.

DENSE_RANK: It returns ranking of data value of mentioned column under the function. Here we get the original rank over the data value of the column.

For example, there are 4 rows exists in a table, if the highest value is exist in  3 rows, then this 3 rows rank will be 1,1,1 and second highest row rank will be 2.

NTILE: It is same as portion concepts in SQL. It display the rows in mentioned number of group.

For example, there are 4 rows exists in a table, if we perform Ntile(2) operation then it will group the 4 rows into 2 set. The 1st and 2^nd row will be 1 and 3^rd and 4^th will be 2.

Like this if there are 9 rows exists in a table, if we perform Ntile(2) operation then it will group the 9 rows into 2 set. The (1st, 2nd, 3rd, 4th)row will be 1 and (5^th,6^th,7^th,8^th,9^th )will be 2. This row grouping is based on rows exists in table.

Now will check this function practically.

select Emp_Id,Name, Salary from Table_a

Now will write all the rank function together and test.

select Name, Salary,

    ROW_NUMBER() over(order by Salary) as [RowNum],

    RANK() over(order by Salary) as [Rank],

    DENSE_RANK() over(order by Salary) as [DenseRank],

    NTILE(3) over(order by Salary) as [nTile]

from Table_a

Output:

Now we understand these function individually from below picture.