datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to use window functions?

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

 

Exam Prep 70-461
Exam 70-461