BETWEEN <window frame bound > AND <window frame bound >
Used with either ROWS or RANGE to specify the lower (starting) and upper (ending) boundary points of the window. <window frame bound> defines the boundary starting point and <window frame bound> defines the boundary end point. The upper bound cannot be smaller than the lower bound.
UNBOUNDED FOLLOWING
Applies to: SQL Server 2012 (11.x) and later.
Specifies that the window ends at the last row of the partition. UNBOUNDED FOLLOWING can only be specified as a window end point. For example RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING defines a window that starts with the current row and ends with the last row of the partition.
<unsigned value specification> FOLLOWING
Specified with <unsigned value specification> to indicate the number of rows or values to follow the current row. When <unsigned value specification> FOLLOWING is specified as the window starting point, the ending point must be <unsigned value specification>FOLLOWING. For example, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING defines a window that starts with the second row that follows the current row and ends with the tenth row that follows the current row. This specification is not allowed for RANGE.
unsigned integer literal
Applies to: SQL Server 2012 (11.x) and later.
Is a positive integer literal (including 0) that specifies the number of rows or values to precede or follow the current row or value. This specification is valid only for ROWS.
More than one window function can be used in a single query with a single FROM clause. The OVER clause for each function can differ in partitioning and ordering.
If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.
Important
If ROWS/RANGE is specified and <window frame preceding>
is used for <window frame extent>
(short syntax) then this specification is used for the window frame boundary starting point and CURRENT ROW is used for the boundary ending point. For example "ROWS 5 PRECEDING" is equal to "ROWS BETWEEN 5 PRECEDING AND CURRENT ROW".
If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification. For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.
Limitations and Restrictions
The OVER clause cannot be used with the DISTINCT aggregations.
RANGE cannot be used with <unsigned value specification> PRECEDING or <unsigned value specification> FOLLOWING.
Depending on the ranking, aggregate, or analytic function used with the OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not be supported.
Examples
A. Using the OVER clause with the ROW_NUMBER function
The following example shows using the OVER clause with ROW_NUMBER function to display a row number for each row within a partition. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD
. The ORDER BY clause in the SELECT statement determines the order in which the entire query result set is returned.
USE AdventureWorks2022;
SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName, s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
Here is the result set.
Row Number LastName SalesYTD PostalCode
--------------- ----------------------- --------------------- ----------
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
B. Using the OVER clause with aggregate functions
The following example uses the OVER
clause with aggregate functions over all rows returned by the query. In this example, using the OVER
clause is more efficient than using subqueries to derive the aggregate values.
USE AdventureWorks2022;
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"
,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
Here is the result set.
SalesOrderID ProductID OrderQty Total Avg Count Min Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659 776 1 26 2 12 1 6
43659 777 3 26 2 12 1 6
43659 778 1 26 2 12 1 6
43659 771 1 26 2 12 1 6
43659 772 1 26 2 12 1 6
43659 773 2 26 2 12 1 6
43659 774 1 26 2 12 1 6
43659 714 3 26 2 12 1 6
43659 716 1 26 2 12 1 6
43659 709 6 26 2 12 1 6
43659 712 2 26 2 12 1 6
43659 711 4 26 2 12 1 6
43664 772 1 14 1 8 1 4
43664 775 4 14 1 8 1 4
43664 714 1 14 1 8 1 4
43664 716 1 14 1 8 1 4
43664 777 2 14 1 8 1 4
43664 771 3 14 1 8 1 4
43664 773 1 14 1 8 1 4
43664 778 1 14 1 8 1 4
The following example shows using the OVER
clause with an aggregate function in a calculated value.
USE AdventureWorks2022;
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)
*100 AS DECIMAL(5,2))AS "Percent by ProductID"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664);
Here is the result set. Notice that the aggregates are calculated by SalesOrderID
and the Percent by ProductID
is calculated for each line of each SalesOrderID
.
SalesOrderID ProductID OrderQty Total Percent by ProductID
------------ ----------- -------- ----------- ---------------------------------------
43659 776 1 26 3.85
43659 777 3 26 11.54
43659 778 1 26 3.85
43659 771 1 26 3.85
43659 772 1 26 3.85
43659 773 2 26 7.69
43659 774 1 26 3.85
43659 714 3 26 11.54
43659 716 1 26 3.85
43659 709 6 26 23.08
43659 712 2 26 7.69
43659 711 4 26 15.38
43664 772 1 14 7.14
43664 775 4 14 28.57
43664 714 1 14 7.14
43664 716 1 14 7.14
43664 777 2 14 14.29
43664 771 3 14 21.4
43664 773 1 14 7.14
43664 778 1 14 7.14
(20 row(s) affected)
C. Producing a moving average and cumulative total
The following example uses the AVG and SUM functions with the OVER clause to provide a moving average and cumulative total of yearly sales for each territory in the Sales.SalesPerson
table. The data is partitioned by TerritoryID
and logically ordered by SalesYTD
. This means that the AVG function is computed for each territory based on the sales year. Notice that for TerritoryID
1, there are two rows for sales year 2005 representing the two sales people with sales that year. The average sales for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.
USE AdventureWorks2022;
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD
,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS MovingAvg
,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;
Here is the result set.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
(10 row(s) affected)
In this example, the OVER clause does not include PARTITION BY. This means that the function will be applied to all rows returned by the query. The ORDER BY clause specified in the OVER clause determines the logical order to which the AVG function is applied. The query returns a moving average of sales by year for all sales territories specified in the WHERE clause. The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD
,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
),1) AS MovingAvg
,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)
),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY SalesYear;
Here is the result set.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93
(10 row(s) affected)
D. Specifying the ROWS clause
Applies to: SQL Server 2012 (11.x) and later.
The following example uses the ROWS clause to define a window over which the rows are computed as the current row and the N number of rows that follow (1 row in this example).
SELECT BusinessEntityID, TerritoryID
,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;
Here is the result set.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62
In the following example, the ROWS clause is specified with UNBOUNDED PRECEDING. The result is that the window starts at the first row of the partition.
SELECT BusinessEntityID, TerritoryID
,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)
ROWS UNBOUNDED PRECEDING),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5;
Here is the result set.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 559,697.56
287 NULL 519,905.93 2006 1,079,603.50
285 NULL 172,524.45 2007 1,252,127.95
283 1 1,573,012.94 2005 1,573,012.94
280 1 1,352,577.13 2005 2,925,590.07
284 1 1,576,562.20 2006 4,502,152.27
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 4,251,368.55
281 4 2,458,535.62 2005 6,709,904.17
Examples: Analytics Platform System (PDW)
E. Using the OVER clause with the ROW_NUMBER function
The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.
-- Uses AdventureWorks
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName, LastName,
CONVERT(VARCHAR(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
Here is a partial result set.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. Using the OVER clause with aggregate functions
The following examples show using the OVER clause with aggregate functions. In this example, using the OVER clause is more efficient than using subqueries.
-- Uses AdventureWorks
SELECT SalesOrderNumber AS OrderNumber, ProductKey,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total,
AVG(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Avg,
COUNT(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Count,
MIN(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Min,
MAX(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Max
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND
ProductKey LIKE '2%'
ORDER BY SalesOrderNumber,ProductKey;
Here is the result set.
OrderNumber Product Qty Total Avg Count Min Max
----------- ------- --- ----- --- ----- --- ---
SO43659 218 6 16 3 5 1 6
SO43659 220 4 16 3 5 1 6
SO43659 223 2 16 3 5 1 6
SO43659 229 3 16 3 5 1 6
SO43659 235 1 16 3 5 1 6
SO43664 229 1 2 1 2 1 1
SO43664 235 1 2 1 2 1 1
The following example shows using the OVER clause with an aggregate function in a calculated value. Notice that the aggregates are calculated by SalesOrderNumber
and the percentage of the total sales order is calculated for each line of each SalesOrderNumber
.
-- Uses AdventureWorksDW2022
SELECT SalesOrderNumber AS OrderNumber, ProductKey AS Product,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER(PARTITION BY SalesOrderNumber) AS Total,
CAST(1. * OrderQuantity / SUM(OrderQuantity)
OVER(PARTITION BY SalesOrderNumber)
*100 AS DECIMAL(5,2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN(N'SO43659',N'SO43664') AND
ProductKey LIKE '2%'
ORDER BY SalesOrderNumber,ProductKey;
The first start of this result set is:
OrderNumber Product Qty Total PctByProduct
----------- ------- --- ----- ------------
SO43659 218 6 16 37.50
SO43659 220 4 16 25.00
SO43659 223 2 16 12.50
SO43659 229 2 16 18.75
See Also
Aggregate Functions (Transact-SQL)
Analytic Functions (Transact-SQL)
Excellent blog post about window functions and OVER, on sqlmag.com, by Itzik Ben-Gan
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for
This product