Check video for proper understanding


----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.
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,NTILE(4) over (partition by catid order by prodid desc) [Ranking]
 from sale_detail order by catid,ranking;

----- Customer with order count
select CustomerID,COUNT(OrderID) [Count] from orders group by CustomerID order by Count desc;


----- Customer with nth highest count
Declare @n int;
set @n=1;
with cust_count(customeid,count)
as
(select CustomerID,COUNT(OrderID) [Count] from orders group by CustomerID
)
select * from cust_count c1 where (@n-1)=(select count(*) from cust_count c2 where c2.count>c1.count);

----- Product Total sale, Max order, Percentage to total sale
with totv(productid,maxsale,total)
as
(
select e.ProductID,e.maxsale,e.total from (select Productid,SUM(quantity) total,MAX(quantity) as maxsale from [Order Details] group by ProductID) e
)
select productid,maxsale,total,convert(float, (convert(money,total)/(select SUM(total) from totv)*100)) [percentage sale] from totv


----- nth Highest salary problem
Use Employee;

declare @n int;
set @n=1;
Select e1.* from tbemp e1 where (@n-1)=(Select COUNT(*) from tbemp e2 where e2.empsal>e1.empsal);


----- No. of Employee in department
Select depnam,COUNT(empno) [No of employee] from tbdep inner join tbemp on depcod=empdepcod group by depnam;

----- No. of employee in department
select depnam,COUNT(empno) [No. of employee] from tbdep left outer join tbemp on depcod=empdepcod group by depnam;

----- City Case
Select empnam,case empcity
                when 'Chandigarh' then 'Tricity'
                when 'Panchkula' then 'Tricity'
                When 'Mohali' then 'Tricity'
                else empcity
                end [City] from tbemp;

----- Get duplicate entries
select * from tbemp e1 where e1.empno!=(select MAX(empno) from tbemp e2 where e1.empnam=e2.empnam);

Click to download Query File

Click here to Download Employee database script