LAG Function - SQL


Overview


The LAG function in SQL provides access to a value in a previous row of the result set, based on an offset. It is useful for comparing values in the current row with values in previous rows.

Example:

SELECT department_id, first_name, salary,
LAG(first_name, 1, 0) OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees

Syntax:

SELECT column_name,
LAG(expression, offset, default_value) OVER (PARTITION BY partition_column[s] ORDER BY order_column[s])
FROM table_name

partition_column[s] is the column or columns that divide the result set into partitions.

order_column[s] is the column or columns that specify the order of rows within each partition.

LAG(expression, offset, default_value) retrieves the expression value from the previous row at the specified offset within each partition. If there is no previous row, it returns the default_value.

Sample Data:

Before

department_id first_name salary
3 Frank 123000
2 Jane 135000
3 Ashley 115000
NULL Glenn 115000
2 Kelly 125000
1 Richard 120000
1 George 105000
5 Kyle 200000
2 James 107000
1 Gustavo 100000

After

department_id first_name salary
NULL Glenn 115000
1 Richard 120000
1 George 105000
1 Gustavo 100000
2 Jane 135000
2 Kelly 125000
2 James 107000
3 Ashley 115000
3 Frank 123000
5 Kyle 200000

Example: Query without the WHERE Statement


In this example, we are returning the department_id, first_name, and salary column, along with a LAG column to return the first name of the employee who ranks one position higher within the same department, ordering their salary in descending order. If there is no higher employee, it returns a 0.


Example: Query with the WHERE Statement


In this example, we are filtering for only rows in department_id 1. We are returning the department_id, first_name, and salary column, along with a LAG column to return the first name of the employee who ranks one position higher within the same department, ordering their salary in descending order. If there is no higher employee, it returns a 0.