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