OFFSET Window Functions

OFFSET  window functions allow you to return value from a row that is at certain offset from current row.

SQL Server 2012 supports four OFFSET functions.

SQL Server 2012 supports four OFFSET functions.

1. LAG()
2. LEAD()
3. FIRST_VALUE()
4. LAST_VALUE()

The LAG() and LEAD() functions support window partition and window order clause. LAG function looks before the current row and LEAD function looks ahead. These functions accepts three arguments. The first argument to the function is the element you want to return,the second argument is the offset,third argument is the default value to return in case there is no row t the requested offset(NULL If not specified).

Example:

select s.SalesOrderID,s.SalesOrderDetailID,

LAG(s.SalesOrderDetailID) over(partition by salesorderid order by SalesOrderDetailID) LagValue,

LEAD(s.SalesOrderDetailID) over(partition by salesorderid order by SalesOrderDetailID) LeadValue

from Sales.SalesOrderDetail s

where salesorderid in (43659)

Because we did not mentioned the offset,the function assumed 1 default. In other words LAG returned the Value immediately previous sales order ID and LEAD immediately next.Also,because we did not mention the third argument,NULL was assumed by default when there was no previous or next row.

FIRST_VALUE() and LAST_VALUE() functions allow you to return an element from the first and last rows in the windows frame.

Example:

SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,

FIRST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) FirststValue,

LAST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LastValue

FROM Sales.SalesOrderDetail s

WHERE SalesOrderID IN (43659,43670)