----Basic but important functions --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.
