-------------------------
--- Ranking Functions ---
-------------------------
----------------
-- ROW_NUMBER --
----------------
-- Objective - Add Row Num Column
 
SELECT SalesOrderID, OrderDate, 
ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS 'RowNumber'
FROM Sales.SalesOrderHeader 

--Order BY OrderDate Desc
SELECT SalesOrderID, OrderDate, 
ROW_NUMBER() OVER (ORDER BY OrderDate desc) AS 'RowNumber'
FROM Sales.SalesOrderHeader 

-- Use PARTITION BY
select ProductID, Name, ProductLine, [Weight],
ROW_NUMBER () OVER (PARTITION BY ProductLine ORDER BY ProductID) as WeightOrder
from  Production.Product	
ORDER  BY ProductLine, WeightOrder

-- RANK
SELECT P.Name, P.ListPrice, PSC.Name Category,
RANK() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice)AS PriceRank
FROM Production.Product P
JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
and PSC.Name = 'Gloves'

-- DENSE_RANK
SELECT P.Name Product, P.ListPrice, PSC.Name Category,
DENSE_RANK() OVER(PARTITION BY PSC.Name ORDER BY P.ListPrice)AS PriceRank
FROM Production.Product P
JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
and PSC.Name = 'Gloves'

Create table RANK_TEST
(
COL1 varchar(10),
COL2 int
)

Insert Into RANK_TEST Values ('A', 1)
Insert Into RANK_TEST Values ('A', 1)
Insert Into RANK_TEST Values ('A', 2)
Insert Into RANK_TEST Values ('A', 3)
Insert Into RANK_TEST Values ('A', 3)
Insert Into RANK_TEST Values ('B', 1)
Insert Into RANK_TEST Values ('B', 2)

select COL1, COL2, 
RANK() OVER(PARTITION BY COL1 ORDER BY COL2)AS RANK
from RANK_TEST

select COL1, COL2, 
DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2)AS RANK
from RANK_TEST

-- NTILE
SELECT PC.Name Category, P.Name Product, P.ListPrice,
NTILE(8) OVER(PARTITION BY PC.Name ORDER BY ListPrice)AS PriceBand
FROM Production.Product P
JOIN Production.ProductSubCategory PSC
ON P.ProductSubCategoryID = PSC.ProductSubCategoryID
JOIN Production.ProductCategory PC
ON PSC.ProductCategoryID = PC.ProductCategoryID

select COL1, COL2, 
NTILE(1) OVER(PARTITION BY COL1 ORDER BY COL2)AS RANK
from RANK_TEST	

select COL1, COL2, 
NTILE(2) OVER(PARTITION BY COL1 ORDER BY COL2)AS RANK
from RANK_TEST

select COL1, COL2, 
NTILE(3) OVER(PARTITION BY COL1 ORDER BY COL2)AS RANK
from RANK_TEST	

select COL1, COL2, 
NTILE(4) OVER(PARTITION BY COL1 ORDER BY COL2)AS RANK
from RANK_TEST	

select COL1, COL2, 
NTILE(5) OVER(PARTITION BY COL1 ORDER BY COL2)AS RANK
from RANK_TEST	
	
SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"
    ,RANK() OVER (ORDER BY a.PostalCode) AS Rank
    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"
    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile
    ,s.SalesYTD
    ,a.PostalCode
FROM Sales.SalesPerson AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;