In T-SQL as everyone knows about OVER() function that is used with ranking functions. It has two options Partition By and Order By. Partition By is optional while Order By Mandatory for ranking functions.
If we think outside of ranking function than we can also use OVER() function with Aggregate functions, Next Value for (used with sequence) and Analytic functions.
OVER() function generates output according to conditions. We can reuse its values using CTEs or we can also insert this values inside the tables.
Some examples:
If we think outside of ranking function than we can also use OVER() function with Aggregate functions, Next Value for (used with sequence) and Analytic functions.
OVER() function generates output according to conditions. We can reuse its values using CTEs or we can also insert this values inside the tables.
Some examples:
SELECT SalesOrderID, ProductID, OrderQty
,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS [Average]
FROM Sales.SalesOrderDetail
SELECT SalesOrderID, ProductID, OrderQty
,ROW_NUMER() OVER(PARTITION BY SalesOrderID Order By SalesOrderID) AS [Average]
FROM Sales.SalesOrderDetail
