Which function you will use if you have to give one rank to one person no duplicate ranks, whether value is same or not.

For this question we will be using a common data set which will be:

CREATE TABLE [dbo].[Employ]

(

            [EmployID] [int] NULL,

            [EmployName] [varchar](30) NULL,

            [EmploySalary] [int] NULL

)

insert into Employ values(8,'emp1',2020)

insert into Employ values(9,'emp2',2070)

insert into Employ values(10,'emp3',2120)

insert into Employ values(11,'emp4',2170)

insert into Employ values(12,'emp5',2220)

insert into Employ values(13,'emp6',2270)

insert into Employ values(14,'emp7',2320)

insert into Employ values(15,'emp8',2370)

insert into Employ values(1,'emp9',2090)

insert into Employ values(2,'emp10',2140)

insert into Employ values(3,'emp11',2190)

insert into Employ values(4,'emp12',2240)

insert into Employ values(5,'emp13',2090)

insert into Employ values(6,'emp14',2140)

insert into Employ values(7,'emp15',2190)

Details about the functions

Rank: it give the similar row a same rank and leaves the rows in between if in duplicate

select EmployID,EmployName,EmploySalary,rank() over(order by employsalary desc) R from Employ

In this employ with same salary have got same rank i.e. 6 and as there are 2 employees with same salary so 7 is missed next rank which is given is 8

Dense Rank: it give the similar row a same rank and but do not leave any the rows in between if in duplicate

select EmployID,EmployName,EmploySalary,dense_rank() over(order by employsalary desc) R from Employ

In this employ with same salary have got same rank i.e. 6 and as there are 2 employees with same salary then also 7 is used for the next employ

Row_number():it give the one rank to every row, no duplicates of ranks in case of similar data then also randomly it gives a number to any row

select EmployID,EmployName,EmploySalary,ROW_NUMBER() over(order by employsalary desc)R from Employ

Similarities between Rank, Dense_rank, row_number:

1. All of them allow order by and partition by clauses

2. In case if the data is not duplicate then result for all the functions will same( if the order by and partition by conditions are same)

3. All of them increase the integer as 1 in case of non-duplicate data

Difference between Rank, Dense_rank, row_number:

1. In case of duplicate data: rank will give the same rank to all the same members but will leave rank before assigning the rank to the next one. But in dense rank it will give same rank to same member next rank will be given to the next non same member, in case of row_number it will assign single rank to single number no duplicate ranks

SELECT EMPLOYID,EMPLOYNAME,EMPLOYSALARY,

RANK() OVER(ORDER BY EMPLOYSALARY DESC)RAN,

DENSE_RANK() OVER(ORDER BY EMPLOYSALARY DESC)DENSE_RAN,

ROW_NUMBER() OVER(ORDER BY EMPLOYSALARY DESC)ROW_NUM FROM EMPLOY

Till rank 5 all are having the same data, as there is not duplicate but after 5 rank as there is duplicate the way of handling data is different.