LEAD Function - SQL
Overview
The LEAD function in SQL provides access to a value in a subsequent row of the result set, based on an offset. It is useful for comparing values in the current row with values in subsequent rows.
Example:
SELECT department_id, first_name, salary,
LEAD(first_name, 1, 0) OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees
Syntax:
SELECT column_name,
LEAD(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 LEAD column to return the first name of the employee who ranks one position lower within the same department, ordering their salary in descending order. If there is no lower 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 LEAD column to return the first name of the employee who ranks one position lower within the same department, ordering their salary in descending order. If there is no lower employee, it returns a 0.
