Use of row_number(),rank(),dense_rank() in SQL SERVER

How to use of row_number(),rank(),dense_rank() in SQL SERVER

In this blog I will explain the ranking functions in Sql server : Three types of ranking functions :-

a. row_number()

b. rank()

c. dense_rank()

First of all I am creating a temp table with data for better understanding. Go through with below query :-

Create table #SaleTable
(
customername varchar(50),
vendorname varchar(50),
product varchar(50),
price money
)

Insert into #SaleTable values ('Ravi','Bata','Shoe',500)
Insert into #SaleTable values ('Isha','Bata','Shoe',300)
Insert into #SaleTable values ('Santosh','Bata','Bag',1000)
Insert into #SaleTable values ('Gaurav','Tommy','T-Shirt',1500)
Insert into #SaleTable values ('Dipak','Tommy','T-Shirt',800)
Insert into #SaleTable values ('Ravi','Tommy','T-Shirt',1500)
Insert into #SaleTable values ('Isha','Madame','Top',2000)

select * from #SaleTable

Result :-

In above result there is no unique column . Now I create unique row number for every row using row_number() function.

Query : -

select ROW_NUMBER() over (order by customername) RowNumber, customername, vendorname, product, price from #Saletable

Result : -

Now,

I want to set rank according to vendorname. Like There are three rows consist tommy so 1,2,3 for this, three rows also consists for bata so 1,2,3 for this, and one row for madame so it is 1.

Query : -

 select ROW_NUMBER() over (order by customername) RowNumber,
		ROW_NUMBER() over (partition by vendorname order by customername) Rankvendor,
		customername,
		vendorname,
		product,
		price
 from #Saletable

Result : -

Now,

I want to get rank on the basis of product. Like 1,1,1 for Shoe, 2 for Bag, 3 fro Top, 4,4,4 for T-shirt.

 select ROW_NUMBER() over (order by customername) RowNumber,
		ROW_NUMBER() over (partition by vendorname order by customername) Rankvendor,
		rank() over (order by product) Rankproduct,
		customername,
		vendorname,
		product,
		price
 from #Saletable

Result : -

I used rank() function to get Rank by product but you see there is a missing rank there 3 and 5.

Actually my data is  repeating in vendorname that's why it is not not able to set rank  with continuous number.

In this case we have to use dense_rank().

Query : -

 select ROW_NUMBER() over (order by customername) RowNumber,
		ROW_NUMBER() over (partition by vendorname order by customername) Rankvendor,
		dense_rank() over (order by product) Rankproduct,
		customername,
		vendorname,
		product,
		price
 from #Saletable

Result : -

using dense_rank() i got the desired result. dense_rank() doesn't skip continuous number. So in this scenario when my data repeats in a column which i want to get ranked.Just use dense_rank().

I think i successfully delivered a concept on Ranking functions. If there is any mistake in above concept Please comment in text. Your comment would be appreciated.

Thank you.