The SQL lead function allows you to access the next row from the current row at a specific offset. In short, the lead function allows you to access the next row from the current one. By specifying the offset value, you can access the next 1, 2, 3, etc., rows from the current one.
It is the opposite of the lag() function, which allows you to access previous rows.
SQL Lead()
The function syntax is as shown:
OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression [ASC | DESC]
);
The following are the supported arguments:
- value_expression – specifies the return value of the preceding row. The expression must evaluate to a single value.
- offset – specifies how many rows forward from the current row to access.
- default – sets the default value if the offset is outside the scope of the partition. By default, the value is set to NULL.
- Partition by – specifies how to partition the data.
- Order by – sets the order format for the rows in each partition.
Example 1
Suppose we have a table containing employee information as shown:
To access the next employee name, we can run a query as shown:
partition BY category
ORDER BY id ASC
) AS next_employee
FROM employees;
The code above will partition the data based on the category. It will then fetch the next name in the partition using the lead function.
The resulting output is as shown:
Example 2
If there is no next row in a specific column (out of bound), the function will set the value to NULL, as shown above.
To set a default value for any out-of-scope access, we can do the following:
partition BY category
ORDER BY id ASC
) AS next_employee
FROM employees;
We set the default value to ‘N/A’. This should replace any out-of-bound value as shown in the output:
NOTE: Setting the offset to 1 is similar to not specifying any value.
Example 3
Suppose you want to access the next two rows from the current row? We can do that by setting the offset value to 2.
An example query is illustrated below:
partition BY category
ORDER BY id ASC
) AS next_employee
FROM employees;
This query will return the next two rows in each partition as shown below:
