SQL Window Functions

This function lists all orders with a running total.

SELECT FirstName, LastName, OrderNumber, TotalAmount, 
       SUM(TotalAmount) OVER (ORDER BY OrderNumber) 
           AS 'Running Total'
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId

FirstName	LastName	OrderNumber	TotalAmount	Running Total
Paul	Henriot	542378	440.00	440.00
Karin	Josephs	542379	1863.40	2303.40
Mario	Pontes	542380	1813.00	4116.40
Mary	Saveley	542381	670.80	4787.20
Pascale	Cartrain	542382	3730.00	8517.20
Mario	Pontes	542383	1444.80	9962.00

Notice how the Running Total increments with each row.

Window Functions fall into three categories.
Each category lists their functions.

Syntax for window functions.

WINDOW ( ALL expression ) 
  OVER ( PARTITION BY partition_list 
         ORDER BY order_list )

SELECT OrderNumber, 
       CAST(OrderDate AS DATE) AS OrderDate,
       CAST(LAG(OrderDate, 1) 
            OVER(ORDER BY OrderDate ASC) AS DATE) 
                 AS 'Prev OrderDate',
       DATEDIFF(day, LAG(OrderDate, 1) 
            OVER(ORDER BY OrderDate ASC), OrderDate) 
                 AS 'Days Between'
  FROM [Order]

LAG returns the previous value of the specified column. As expected, the first record returns NULL values.

OrderNumber	OrderDate	Prev OrderDate	Days Between
542378	2012-07-04	NULL	NULL
542379	2012-07-05	2012-07-04	1
542380	2012-07-08	2012-07-05	3
542381	2012-07-08	2012-07-08	0
542382	2012-07-09	2012-07-08	1
542383	2012-07-10	2012-07-09	1
542384	2012-07-11	2012-07-10	1
542385	2012-07-12	2012-07-11	1
542386	2012-07-15	2012-07-12	3

SELECT FirstName, LastName, City, Country, 
       RANK() OVER(PARTITION BY Country ORDER BY City) 
                   AS Rank
  FROM Customer

RANK returns the sequential number of a row within a partition of a result set. It is similar to ROW_NUMBER, except that RANK returns the same numeric value with ties.

FirstName	LastName	City	Country	Rank
Patricio	Simpson	Buenos Aires	Argentina	1
Yvonne	Moncada	Buenos Aires	Argentina	1
Sergio	Gutiérrez	Buenos Aires	Argentina	1
Roland	Mendel	Graz	Austria	1
Georg	Pipps	Salzburg	Austria	2
Catherine	Dewey	Bruxelles	Belgium	1
Pascale	Cartrain	Charleroi	Belgium	2
André	Fonseca	Campinas	Brazil	1
Paula	Parente	Campinas	Resende	2

SELECT FirstName, LastName, City, OrderNumber, TotalAmount,
       SUM(TotalAmount) OVER(PARTITION BY City) 
           AS 'Total Spent',
       ROW_NUMBER() OVER(PARTITION BY City ORDER BY TotalAmount) 
           AS 'Row Number'
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId

ROW_NUMBER returns a sequential number of a row within a partition of a result set.

FirstName	LastName	City	OrderNumber	TotalAmount	Total Spent	Row Number
Palle	Ibsen	Århus	542521	86.40	3763.21	1
Palle	Ibsen	Århus	543197	86.85	3763.21	2
Palle	Ibsen	Århus	542927	420.00	3763.21	3
Palle	Ibsen	Århus	542493	447.20	3763.21	4
Palle	Ibsen	Århus	542955	1030.76	3763.21	5
Palle	Ibsen	Århus	543166	1692.00	3763.21	6
Paula	Wilson	Albuquerque	542392	624.80	52245.90	1
Paula	Wilson	Albuquerque	542891	624.80	52245.90	2
Paula	Wilson	Albuquerque	542699	977.50	52245.90	3
Paula	Wilson	Albuquerque	543130	1075.00	52245.90	4

• Our Sql Aggregate Functions Reference
• Our Sql Functions Reference
• Our Sql SELECT Reference
• Our Sql ORDER BY Reference