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
*/