Transact SQL 101 - Lesson 08_02_SQL.txt

--ROW_NUMBER function ordered by City
SELECT
	ROW_NUMBER() OVER (ORDER BY city) AS 'Row Number',
	CountryRegion,  	
	StateProvince, 
	City	
FROM saleslt.address


--ROW_NUMBER example using the PARTITION BY clause  
SELECT
	ROW_NUMBER() OVER (PARTITION BY stateprovince ORDER BY city) AS 'Row Number',
	CountryRegion,  	
	StateProvince, 
	City	
FROM saleslt.address


--RANK Function example; Rank Products by Price
SELECT
	RANK() OVER (ORDER BY listprice) As Rank,
	Name, ProductNumber, listprice
FROM saleslt.product


--RANK function using the PARTITION BY clause
SELECT
  RANK() OVER (PARTITION BY stateprovince ORDER BY totaldue DESC) as Rank,
  a.stateprovince,
  c.companyname, 
  s.totaldue
FROM saleslt.salesorderheader s
	INNER JOIN saleslt.address a ON s.shiptoaddressid = a.addressid
	INNER JOIN saleslt.customer c ON s.customerid = c.customerid

--NTILE example
SELECT 
  NTILE(4) OVER (ORDER BY totaldue DESC) AS GroupNum,
  salesordernumber, 
  totaldue 
FROM saleslt.salesorderheader


ROW NUMBER SAMPLE

SELECT 
	ROW_NUMBER() OVER (ORDER BY totaldue) as 'Row Number',
	SalesOrderId, 
	SalesOrderNumber, 
	CustomerID, 
	TotalDue
FROM saleslt.salesorderheader 


SELECT
	ROW_NUMBER() OVER (ORDER BY city) AS 'Row Number',
	CountryRegion,  	
	StateProvince, 
	City	
FROM saleslt.address



SELECT
	ROW_NUMBER() OVER (PARTITION BY stateprovince ORDER BY city) AS 'Row Number',
	CountryRegion,  	
	StateProvince, 
	City	
FROM saleslt.address