Tuesday, February 11, 2014

Difference between RANK() and DENSE_RANK() functions?

RANK(): Returns the rank of each row in the result set of partitioned column

select Name,Subject,Marks,
RANK() over(partition by name order by Marks desc)Rank
From ExamResult
order by name,subject


DENSE_RANK() This is same as RANK() function. Only differencec is returns rank with out gaps.
select  Name,Subject,Marks,
DENSE_RANK() over(partition by name order by Marks desc)Rank
From ExamResult
order by name


in RANK() result set screeshot, you can notice that there is gap in Rank(2) for the name Sam and same gap is removed in DENSE_RANK().

No comments:

Post a Comment