Understanding Ranking Functions in Sql Server

Three types of ranking functions :-

• row_number()
• rank()
• dense_rank()

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

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.

Query

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