OVER Clause (Transact-SQL)
Determines the partitioning and ordering of the rowset before the associated window function is applied.
Applies to:
Ranking window functions
Aggregate window functions. For more information, see Aggregate Functions (Transact-SQL).
- PARTITION BY
Divides the result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
- value_expression
Specifies the column by which the rowset produced by the corresponding FROM clause is partitioned. value_expression can only refer to columns made available by the FROM clause. value_expression cannot refer to expressions or aliases in the select list. value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.
- <ORDER BY Clause>
Specifies the order to apply the ranking window function. For more information, see ORDER BY Clause (Transact-SQL).
ImportantWhen used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.
Window functions are defined in the ISO SQL standard. SQL Server provides ranking and aggregate window functions. A window is a user-specified set of rows. A window function computes a value for each row in a result set derived from the window.
More than one ranking or aggregate window function can be used in a single query with a single FROM clause. However, the OVER clause for each function can differ in partitioning and also ordering. The OVER clause cannot be used with the CHECKSUM aggregate function.
A. Using the OVER clause with the ROW_NUMBER function
Each ranking function, ROW_NUMBER, DENSE_RANK, RANK, NTILE uses the OVER clause. The following example shows using the OVER clause with ROW_NUMBER.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
B. 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.
USE AdventureWorks;
GO
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);
GO
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 AdventureWorks;
GO
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);
GO
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.43 |
43664 | 773 | 1 | 14 | 7.14 |
43664 | 778 | 1 | 14 | 7.14 |
