LAG - LEAD in SQL

LAG  (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

 

LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

 

• value_expression - Can be a column or a built-in function, except for other analytic functions.
• offset - The number of rows preceeding/following the current row, from which the data is to be retrieved. The default value is 1.
• default - The value returned if the offset is outside the scope of the window. The default value is NULL.

LAG-  To Compare  Previous Row (offset)

LEAD- To Compare  Next Row (offset)

Example

 

SELECT empno,

       ename,

       job,

       sal,

       LAG(sal, 1 ,0) OVER (ORDER BY sal) AS sal_prev,

       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff

FROM   emp