What is difference between RANK and DENSE_RANK function in SQL Server?

Posted by Kundan64 under Sql Server on 1/17/2013 | Points: 10 | Views : 8413 | Status : [Member] | Replies : 3
What is difference between RANK and DENSE_RANK function in SQL Server?




Responses

Posted by: Sourabh07 on: 1/17/2013 [Member] Starter | Points: 25

Up
0
Down
The RANK() returns the position of a value within the partition of a result set, with gaps in the ranking where there are ties.

The DENSE_RANK() returns the position of a value within the partition of a result set, with no gaps in the ranking where there are ties.

For detail please refer below url - http://www.sqlservercurry.com/2009/04/rank-vs-denserank-with-example-using.html

Sourabh07

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/19/2013 [Member] Starter | Points: 25

Up
0
Down


Create TAble #t (name varchar(10) )

insert into #t
Select 'Neeraj' union ALL
Select 'Vicky' union ALL

Select 'Vikas' union ALL
Select 'Neeraj'








Select rank() over( order by name), name FRom #t


Select

row_number() over(order by name) [row_number] ,
rank() over( order by name) [rank],

dense_rank() over( order by name)[dense_rank] ,
name FRom #t







www.tutorialsqlserver.com


Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jporter892 on: 9/10/2013 [Member] Starter | Points: 25

Up
0
Down
As succinctly as possible: RANK() skips ties, leaving non-consecutive numbers for the ranks whereas DENSE_RANK() doesn't.

Source:

http://www.programmerinterview.com/index.php/database-sql/rank-versus-dense_rank/

Kundan64, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response