Understanding Ranking Functions in Sql Server

Three types of ranking functions :-

  • row_number()
  • rank()
  • dense_rank()
  1. Create table #SaleTable  
  2. (  
  3. customername varchar(50),  
  4. vendorname varchar(50),  
  5. product varchar(50),  
  6. price money  
  7. )   
  8.   
  9. Insert into #SaleTable values ('Ravi','Bata','Shoe',500)  
  10. Insert into #SaleTable values ('Isha','Bata','Shoe',300)  
  11. Insert into #SaleTable values ('Santosh','Bata','Bag',1000)  
  12. Insert into #SaleTable values ('Gaurav','Tommy','T-Shirt',1500)  
  13. Insert into #SaleTable values ('Dipak','Tommy','T-Shirt',800)  
  14. Insert into #SaleTable values ('Ravi','Tommy','T-Shirt',1500)  
  15. Insert into #SaleTable values ('Isha','Madame','Top',2000)  
  16.   
  17. select * from #SaleTable  
table

Query

  1. 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

  1. 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

  1. 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