|
Execute the following
Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate how to use window and ranking functions.
-- SQL Server row_number - SQL Server window functions - SQL Server ranking functions
USE AdventureWorks;
SELECT PO = PurchaseOrderID,
VendorID,
ShipDate = convert(VARCHAR,ShipDate,111),
SeqNo = Row_Number()
OVER(ORDER BY PurchaseOrderID)
FROM Purchasing.PurchaseOrderHeader
WHERE YEAR(OrderDate) = 2003
AND MONTH(OrderDate) = 8
ORDER BY SeqNo
GO
/* Partial results
PO VendorID ShipDate SeqNo
405 13 2003/08/22 1
406 51 2003/08/22 2
407 80 2003/08/22 3
408 47 2003/08/22 4
409 81 2003/09/01 5
410 46 2003/09/01 6
411 21 2003/09/01 7
*/
-- SQL Server row_number over partition by - SQL windowing functions
SELECT PO = PurchaseOrderID,
VendorID,
ShipDate = convert(VARCHAR,ShipDate,111),
SeqNo = Row_Number()
OVER(PARTITION BY convert(VARCHAR,ShipDate,111)
ORDER BY PurchaseOrderID)
FROM Purchasing.PurchaseOrderHeader
WHERE YEAR(OrderDate) = 2003
AND MONTH(OrderDate) = 5
ORDER BY PurchaseOrderID
GO
/* Partial results
PO VendorID ShipDate SeqNo
297 3 2003/05/16 1
298 54 2003/05/16 2
299 104 2003/05/16 3
300 25 2003/05/16 4
301 90 2003/05/23 1
302 33 2003/05/23 2
303 41 2003/05/23 3
*/
-- SQL ranking functions
USE AdventureWorks
SELECT FirstName + ' ' + LastName AS SalesStaff,
CONVERT(VARCHAR,OrderDate,111) AS OrderDate,
Row_Number()
OVER(ORDER BY SalesPersonID) AS SeqNo,
RANK()
OVER(ORDER BY SalesPersonID) AS [RANK],
DENSE_RANK()
OVER(ORDER BY SalesPersonID) AS DenseRank,
NTILE(10000)
OVER(ORDER BY SalesPersonID) AS [NTile]
FROM Sales.SalesOrderHeader soh
INNER JOIN HumanResources.Employee e
ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE OrderDate = '2002-02-01'
AND SalesPersonID IS NOT NULL
ORDER BY SalesPersonID
GO
/* Partial results
SalesStaff OrderDate SeqNo RANK DenseRank NTile
José Saraiva 2002/02/01 73 64 9 73
José Saraiva 2002/02/01 74 64 9 74
José Saraiva 2002/02/01 75 64 9 75
José Saraiva 2002/02/01 76 64 9 76
David Campbell 2002/02/01 77 77 10 77
David Campbell 2002/02/01 78 77 10 78
David Campbell 2002/02/01 79 77 10 79
*/
-- T-SQL ranking functions - MSSQL windowing functions
SELECT FirstName + ' ' + LastName AS SalesStaff,
CONVERT(VARCHAR,OrderDate,111) AS OrderDate,
Row_Number()
OVER(ORDER BY SalesPersonID, SalesOrderID) AS RowNo,
Row_Number()
OVER(PARTITION BY SalesPersonID ORDER BY SalesPersonID) AS SeqNo,
RANK()
OVER(ORDER BY SalesPersonID) AS [RANK],
DENSE_RANK()
OVER(ORDER BY SalesPersonID) AS DenseRank,
NTILE(10000)
OVER(PARTITION BY SalesPersonID ORDER BY SalesPersonID) AS [NTile]
FROM Sales.SalesOrderHeader soh
INNER JOIN HumanResources.Employee e
ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c
ON e.ContactID = c.ContactID
WHERE OrderDate = '2002-02-01'
AND SalesPersonID IS NOT NULL
ORDER BY SalesPersonID
GO
/* Partial results
SalesStaff OrderDate RowNo SeqNo RANK DenseRank NTile
José Saraiva 2002/02/01 72 9 64 9 9
José Saraiva 2002/02/01 73 10 64 9 10
José Saraiva 2002/02/01 74 11 64 9 11
José Saraiva 2002/02/01 75 12 64 9 12
José Saraiva 2002/02/01 76 13 64 9 13
David Campbell 2002/02/01 77 1 77 10 1
David Campbell 2002/02/01 78 2 77 10 2
David Campbell 2002/02/01 79 3 77 10 3
*/
-- SQL over partition by - SQL Server row_number - SQL Server windowing functions
-- SQL Server ranking functions - SQL inner join
SELECT pod.PurchaseOrderID AS PO,
OrderQty,
ROW_NUMBER()
OVER(PARTITION BY pod.PurchaseOrderID ORDER BY OrderQty DESC) AS [SeqNo],
RANK()
OVER(PARTITION BY pod.PurchaseOrderID ORDER BY OrderQty DESC) AS [Rank],
DENSE_RANK()
OVER(PARTITION BY pod.PurchaseOrderID ORDER BY OrderQty DESC) AS [DenseRank]
FROM AdventureWorks.Purchasing.PurchaseOrderDetail pod
INNER JOIN AdventureWorks.Purchasing.PurchaseOrderHeader poh
ON poh.PurchaseOrderID = pod.PurchaseOrderID
WHERE OrderDate = '2004-04-01'
AND OrderQty > 1
ORDER BY pod.PurchaseOrderID,
SeqNo
GO
/* Partial results
PO OrderQty SeqNo Rank DenseRank
4007 5000 1 1 1
4007 5000 2 1 1
4007 5000 3 1 1
4007 1050 4 4 2
4007 1000 5 5 3
4007 1000 6 5 3
4007 750 7 7 4
4007 750 8 7 4
4007 750 9 7 4
*/
|
