Sql server® 2012 t-sql fundamentals

Offset Window Functions

Offset window functions allow you to return an element from a row that is at a certain offset from the 

current row or from the beginning or end of a window frame. SQL Server 2012 supports four offset 

functions: LAG and LEAD, and FIRST_VALUE and LAST_VALUE.

The LAG and LEAD functions support window partition and window order clauses. There’s no 

relevance to window framing here. These functions allow you to obtain an element from a row that is 

at a certain offset from the current row within the partition, based on the indicated ordering. The LAG 

function looks before the current row, and the LEAD function looks ahead. The first argument to the 

functions (which is mandatory) is the element you want to return; the second argument (optional) is 

the offset (1 if not specified); the third argument (optional) is the default value to return in case there 

is no row at the requested offset (NULL if not specified).

www.it-ebooks.info

---

218  

Microsoft SQL Server 2012 T-SQL Fundamentals

As an example, the following query returns order information from the OrderValues view. For each 

customer order, the query uses the LAG function to return the value of the previous customer’s order 

and the LEAD function to return the value of the next customer’s order.

SELECT custid, orderid, val, 

  LAG(val)  OVER(PARTITION BY custid 

                 ORDER BY orderdate, orderid) AS prevval, 

  LEAD(val) OVER(PARTITION BY custid 

                 ORDER BY orderdate, orderid) AS nextval 

FROM Sales.OrderValues;

Here’s the output of this query in abbreviated form.

custid  orderid  val      prevval  nextval 

------- -------- -------- -------- -------- 

1       10643    814.50   NULL     878.00 

1       10692    878.00   814.50   330.00 

1       10702    330.00   878.00   845.80 

1       10835    845.80   330.00   471.20 

1       10952    471.20   845.80   933.50 

1       11011    933.50   471.20   NULL 

2       10308    88.80    NULL     479.75 

2       10625    479.75   88.80    320.00 

2       10759    320.00   479.75   514.40 

2       10926    514.40   320.00   NULL 

3       10365    403.20   NULL     749.06 

3       10507    749.06   403.20   1940.85 

3       10535    1940.85  749.06   2082.00 

3       10573    2082.00  1940.85  813.37 

3       10677    813.37   2082.00  375.50 

3       10682    375.50   813.37   660.00 

3       10856    660.00   375.50   NULL 

... 

 

(830 row(s) affected)

Because you didn’t indicate the offset, the functions assumed 1 by default; in other words, LAG ob-

tained the value of the immediately previous customer’s order, and LEAD from the immediately next. 

Also, because you didn’t specify a third argument, NULL was assumed by default when there was no 

previous or next row. The expression LAG(val, 3, 0) would obtain the value from three rows back and 

would return 0 if a row wasn’t found.

In this example, I just returned the values from the previous and next orders, but normally you would 

compute something based on the returned values. For example, you could compute the difference 

between the current customer’s order value and that of the previous customer’s: val - LAG(val) OVER(…), 

or the difference from the next: val - LEAD(val) OVER(…).

The FIRST_VALUE and LAST_VALUE functions allow you to return an element from the first and last 

rows in the window frame, respectively. Therefore, these functions support window partition, order, 

and frame clauses. If you want the element from the first row in the window partition, use FIRST_VALUE 

www.it-ebooks.info

---