–ROW_NUMBER()
–RANK()
–DENSE_RANK()
–NTILE()
–ROW_NUMBER() –Returns a sequential row number with in partition of result set
use Northwind;
SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products;
—-Getting alternate rows
——Odd Row
select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=1;
——Even Row
select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]%2=0;
——Row Between
select * from (SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products) p where p.[Row No.]
Between 5 and 10;
—— With CTE (commmon type expression)
with result_with_row_no([Row No.],ProductName)
as
(SELECT ROW_NUMBER() over(order by productid) as [Row No.], ProductName from Products)
select * from result_with_row_no where [Row No.]%2=1;
—— RANK() –Returns the rank of each row within the partition of a result set
select ProductID,COUNT(OrderID) [Number sold], RANK() OVER(order by count(orderid)) [Ranking] from [Order Details]
group by ProductID order by ranking,[Number sold];
—— DENSE_RANK() —-Returns the rank of each row within the partition of a result set without gap;
select ProductID,COUNT(OrderID) [Number sold], DENSE_RANK() OVER(order by count(orderid)) [Ranking] from [Order Details]
group by ProductID order by ranking,[Number sold];
—– Complex Example — getting product total quantity sale with group category and rank by sale
with sale_detail(CatID,ProdID,Prod_Name,Sale)
as
(
select CategoryID,p.ProductID, p.ProductName, COUNT(o.productid) as [Sale] from Products p,[Order Details] o
where p.ProductID=o.ProductID group by CategoryID,p.ProductID,p.ProductName
)
select catid,prodid,prod_name,sale,RANK() over (partition by catid order by sale desc) [Ranking]
from sale_detail order by catid,ranking;
—– NTILE –Distributes the rows in an ordered partition into a specified number of groups.