SQL LEAD Function
SQL LEAD function is used by the user if he wants to check or compare value present in the next rows With the current row.
Syntax:
LEAD(COLUMN_NAME, OFFSET)The first argument of LEAD function is the column name from which you want to fetch the value. The offset value is the number of rows forward from the current row from where you want to fetch the value
EXAMPLE:
We are going to use the EMP table for example-
Lets write a LEAD function where OFFSET value is 1 and the LEAD column is SALARY
SELECT *
, LEAD(SALARY, 1) OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMP_ID) AS X
FROM EMP;
AS You can see our LEAD function OFFSET is 1 so all rows are obtaining values from the next line. The Fourth Value is NULL because we partitioned the table by ‘department_id’ , our LEAD window function can’t access rows where ‘department_id’ is different than the current one.
SELECT *
, LEAD(SALARY,2) OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMP_ID) AS X
FROM EMP;
OFFSET is 2 , so that is why we have two NULLs in First partition and so on.
SQL LAG Function
SQL LAG function helps the user in accessing the values present in preceding rows.
Syntax:
LAG( column_name, OFFSET)The first argument of LAG function is the column name from which you want to fetch the value. The offset value is the number of rows backward from the current row from where you want to fetch the value.
EXAMPLE:
For example purpose we are going to use EMP table
SELECT *
, LAG(SALARY, 1) OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMP_ID) AS X
FROM EMP;
First row contains NULL value because LAG function with OFFSET of 1 . (No row exists above the first row)
SELECT *
, LAG(SALARY,2) OVER (PARTITION BY DEPARTMENT_ID ORDER BY EMP_ID) AS X
FROM EMP;
First and Second rows contain NULLs because the OFFSET is 2.
Thanks For Reading!
Source: Microsoft T-SQL Documentation for LEAD/LAG Functions
