Ranking functions in SQL SERVER 2008

CGN007
Posted by CGN007 under Sql Server category on | Points: 40 | Views : 1483
Difference between ROW_NUMBER, RANK and DENSE_RANK

SELECT emp_id,
emp_name,
emp_salary,
ROW_NUMBER () OVER (ORDER BY emp_salary DESC) [ROW_NUMBER],
RANK () OVER (ORDER BY emp_salary DESC) [RANK],
DENSE_RANK () OVER (ORDER BY emp_salary DESC) [DENSE_RANK]

FROM Employee




RESULT SET
emp_id   emp_name	emp_salary	ROW_NUMBER    RANK   DENSE_RANK
100 Sachin 10000 1 1 1
101 Rahul 8000 2 2 2
102 Sourav 8000 3 2 2
103 Sunil 7000 4 4 3
104 Madhav 6000 5 5 4



--> ROW_NUMBER () Simply displays the row number of the result set
-->RANK() assigns same number to ties,but create a gap.So same rank 2 for salary 8000 but rank 4 for the next salary 7000
-->DENSE_RANK() assigns same number to ties,but does not create a gap.So same rank 2 for salary 8000 and rank 3for the next salary 7000
-->ROW_NUMBER, RANK and DENSE_RANK will produce the same result if there are no duplicate values in the sorted result set

Comments or Responses

Login to post response