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.
