The Lead and Lag functions returns subsequent row for the Lead() while previous row for the Lag function.

Syntax

Lead(Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)

LAG (Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)

Column :

• • Column specifies the column value to be returned or expression to return.

OFFSET :

• • The number of rows forward from the current row from the first row. Default value for offset is 1.

Default value:

• The default value is set if LEAD() returns NULL.

Here we have below mentioned table records which contains Project details and their respective Business Stakeholders.

So Now we want to see the next project that is needed to be done once the subsequent project gets completed.

So now i want to see my subsequent project with the use of LEAP() function as shown below:

Using LAG() to get the previous row from the column as shown below:

Setting default value to the LAG and LEAD() when functions returns NULL.

We have discussed OFFSET ?(OPTIONAL),DEFAULT VALUE) in syntax. The offset value defines the number of rows forward or back with LEAD() and LAG(). While Default defines the value when function returns NULL. So in order to handle the same we will set default value ‘NoProject’

Happy Learning