Diffrence between Rank, Dense_rank and Row_number

          Using these three functions we can calculate RowID for a query result set. These three functions works slightly different way.

Example:

Table Creation:

CREATE TABLE TEST

(

ID INT,

NAME VARCHAR(10),

SALARY NUMERIC(10,4)

)

INSERT INTO TEST VALUES (1,'A',500)

                        ,(2,'B',500)

                        ,(3,'C',500)

                        ,(4,'D',600)

                        ,(5,'E',600)

                        ,(6,'F',700)

Sample Query:

          In the above test table is having 'Salary' column is there, based on salary i am going to generate RowID using row_number, Rank and Desne_rank.

SELECT *

,ROW_NUMBER() OVER (ORDER BY SALARY) AS ROWNUMBER

,RANK() OVER (ORDER BY SALARY) AS RANKS

,DENSE_RANK() OVER (ORDER BY SALARY) AS DENSRANK

FROM TEST

O/P:

ROW_NUMBER():

·        This function will assign a unique id to each record returned from the query.

RANK():

·        In the above example 500 is the minimum value so rank is 1. 500 is repeated for three times so same rank is assigned. 

·        Next value is 600 but rank is 4, because previous rank '1'(For salary 500) is repeated three times so the rank function is skipped three numbers and assigned rank 4.

·        Next value is 700 rank is 6, because the previous rank '4' is repeated two times so next rank is 6.

DENSE_RANK()

·        This function is similar to Rank, the difference is, this will not leave gaps between groups.