OVER Clause (Transact-SQL)

Syntax

Ranking Window Functions 
< OVER_CLAUSE > :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

Aggregate Window Functions 
< OVER_CLAUSE > :: = 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

Arguments

• 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).

  Important

  When 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.

Remarks

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.

Examples

A. Using the OVER clause with the ROW_NUMBER function

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS "Row Number"
    ,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;
GO

B. Using the OVER clause with aggregate functions

USE AdventureWorks2008R2;
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

USE AdventureWorks2008R2;
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

See Also

Reference