## Simulate LEAD and LAG functions using other analytic functions

Puzzle: Simulate LEAD and LAG functions using other analytic functions

Level: Intermediate

Solution:

Let’s say that we need to see every employee’s name and 2 more employees who were hired right before. The expected result may look like this:

```ENAME      HIREDATE  EMPL1      EMPL2
---------- --------- ---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81 SMITH
WARD       22-FEB-81 ALLEN      SMITH
JONES      02-APR-81 WARD       ALLEN
BLAKE      01-MAY-81 JONES      WARD
CLARK      09-JUN-81 BLAKE      JONES
TURNER     08-SEP-81 CLARK      BLAKE
MARTIN     28-SEP-81 TURNER     CLARK
KING       17-NOV-81 MARTIN     TURNER
JAMES      03-DEC-81 KING       MARTIN
FORD       03-DEC-81 JAMES      KING
MILLER     23-JAN-82 FORD       JAMES
SCOTT      19-APR-87 MILLER     FORD
```

It is a no-brainer task if we employ LAG function:

```SELECT ename, hiredate,
LAG(ename,1) OVER(ORDER BY hiredate) empl1,
LAG(ename,2) OVER(ORDER BY hiredate) empl2
FROM emp;
```

One of the possible approaches is to use ROWS window attribute with MIN/MAX analytic functions:

```SELECT ename, hiredate,
MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) empl1,
MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) empl2
FROM emp;
```

Common rule is:

```LAG(value_expr,offset,default) OVER ([partition_clause] order_by_clause )
```

is the same as

```NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause
ROWS BETWEEN offset PRECEDING AND offset PRECEDING), default)
```

and

```LEAD(value_expr,offset,default) OVER ([partition_clause] order_by_clause )
```

is the same as

```NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause
ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), default)
```

This substitution becomes essential in other RDBMS where MIN/MAX analytic functions are supported while LEAD/LAG are not. Teradata is one of the examples.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.