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.
