loading..
   English
14:14

LAG and LEAD functions

Syntax:

  1. LAG | LEAD (scalar_expression [,OFFSET] [,DEFAULT])
  2. OVER ( [ partition_by_clause ] order_by_clause )

Window functions LAG and LEAD have appeared in SQL Server in version 2012.

These functions return value of expression calculated for previous (LAG) or next (LEAD) row of the result set respectively. Let's consider the example of simple query that deduces codes of printers along with codes of previous and next rows:

Console
Execute
  1. SELECT code,
  2. LAG(code) OVER(ORDER BY code) prev_code,
  3. LEAD(code) OVER(ORDER BY code) next_code
  4. FROM printer;

code prev_code next_code
1 NULL 2
2 1 3
3 2 4
4 3 5
5 4 6
6 5 NULL

Notice that if the next or previous row (in the ascending order of code values) does not exist, NULL value is used. However this behavior can be changed with aid of optional argument (the third one) of each function. Value of this argument will be used in the case when the corresponding row does not exist. In the following example the value of -999 is used when the previous row does note. In the following example, the one row is missed.

Console
Execute
  1. SELECT code,
  2. LAG(code,1,-999) OVER(ORDER BY code) prev_code,
  3. LEAD(code,1,999) OVER(ORDER BY code) next_code
  4. FROM printer;

code prev_code next_code
1 -999 2
2 1 3
3 2 4
4 3 5
5 4 6
6 5 999

To apply the third argument, we have been forced to use the second optional argument with value of 1 which is a default for this argument. This argument defines which row among previous rows (or the next ones) should be used with respect to the current row. In the following example, the second row with respect to current row is used.

Console
Execute
  1. SELECT code,
  2. LAG(code,2,-999) OVER(ORDER BY code) prev_code,
  3. LEAD(code,2,999) OVER(ORDER BY code) next_code
  4. FROM printer;

code prev_code next_code
1 -999 3
2 -999 4
3 1 5
4 2 6
5 3 999
6 4 999

Finally note that the order in which the previous or next rows are being picked is given by the ORDER BY clause in OVER clause, but not by the order used for the sorting the query result set. The following query illustrates what was being said.

Console
Execute
  1. SELECT code,
  2. LAG(code) OVER(ORDER BY code) prev_code,
  3. LEAD(code) OVER(ORDER BY code) next_code
  4. FROM printer
  5. ORDER BY code DESC;

code prev_code next_code
6 5 NULL
5 4 6
4 3 5
3 2 4
2 1 3
1 NULL 2

Let's consider the "classic" solutions for this task to compare these ones with new approach in SQL.

Self join

Console
Execute
  1. SELECT p1.code,p3.code,p2.code
  2. FROM printer p1 LEFT JOIN Printer p2 ON p1.code=p2.code-1
  3. LEFT JOIN Printer p3 ON p1.code=p3.code+1;

Correlated subqueries

Console
Execute
  1. SELECT p1.code,
  2. (SELECT MAX(p3.code) FROM Printer p3 WHERE p3.code < p1.code) prev_code,
  3. (SELECT MIN(p2.code) FROM Printer p2 WHERE p2.code > p1.code) next_code
  4. FROM printer p1;

Suggested exercises: 126, 130

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100