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

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

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

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