LEAD

MySQL

LEAD(value_expression [, offset [, default_value]]) OVER ( [ partition_by_clause ] order_by_clause )

• value_expression: It is an argument defining the value for which the lead value is returned. The expression should return a single value. It could be any valid expression or column of a table.
• offset: This optional argument specifies the number of rows forward from the current row from which to return the value. The default is 1, meaning it will return the value in the next row.
• default_value: Another optional parameter, it specifies the value to return if the offset goes beyond the scope of the result set.
• partition_by_clause: A part of the OVER () clause, it divides the result set into partitions. The LEAD function is applied to each partition separately and restarts the counting in every new partition.
• order_by_clause: Also part of the OVER () clause. This defines the logical order of the data rows in each partition by one or more columns within the scope of the function. The LEAD function applies to this ordered list.

Example

SELECT
  id,
  value,
  LEAD(value) OVER (ORDER BY id) as next_value
FROM
  myTable;

Output

| id | value | next_value |
|----|-------|------------|
|  1 |   10  |     20     |
|  2 |   20  |     30     |
|  3 |   30  |   NULL     |

Explanation

The aforementioned SQL code includes the LEAD() function, which is utilized to retrieve the subsequent row’s data. In our case, the LEAD(value) function is ordered by id, envisioning the following value in the dataset. The output signifies that for id 1, the following value is 20, and for id 2, the succeeding value is 30. The NULL in the last row’s next_value column indicates that there is no follow-up row for id 3.

PostgreSQL

LEAD(value_expr [, offset] [, default_value]) OVER ([PARTITION BY partition_expression, … ] ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST}], …)

• value_expr: This is the expression evaluated at the leading row, which can be a function, field of a table, or a combination of field(s), function(s), and constant(s).
• offset: This is an optional parameter specifying the number of rows to lead. If not specified, the default is 1. It needs to be an integer.
• default_value: An optional parameter which is used when the offset leads beyond the scope of the window. If not provided, the default value is NULL.
• partition by partition_expression: An optional clause which splits the data into different partitions. The LEAD() function is applied separately for each partition. It can be a column or a set of columns.
• order by sort_expression [asc | desc]: This clause is mandatory and specifies the column(s) for PostgreSQL to sort rows in each partition. The ASC or DESC keyword is optional, indicating the ascending or descending order respectively.
• nulls {first | last}: This optional clause decides the position of NULL values. If NULLS FIRST is used, the NULL values are ranked first. If NULLS LAST is used, the NULL values are ranked last.

Example

SELECT
    name,
    sales,
    LEAD(sales) OVER(ORDER BY sales) AS lead_sales
FROM
    sales_data;

Output

name	sales	lead_sales
John	50	75
Sarah	75	100
Mike	100	Null

Explanation

The LEAD() function is a window function in SQL that provides access to a row at a specified physical offset that follows the current row. In the example, it is used to access the sales value of the next row in the sales_data table. For instance, John’s lead_sales value is 75 which is Sarah’s sales value. For Mike who is the last in the list, there is no following row hence the Null value.

SQL Server

LEAD(value_expression [, offset] [, default]) OVER ( [ partition_by_clause ] order_by_clause )

• value_expression: This is the column or expression that the LEAD function will be applied to.
• offset: An optional parameter, it specifies the number of rows forward from the current row from which to retrieve a value. If not specified, the offset defaults to 1.
• default: Another optional parameter, it specifies the value to return when the LEAD function tries to go beyond the total rows in the table. If not specified, default returns NULL.
• partition_by_clause: It divides the result set into partitions of rows sharing the same values and operates on each partition independently. It is optional.
• order_by_clause: It orders the rows within each partition. The LEAD function can only operate on ordered rows.

Example

SELECT
    EmployeeID,
    OrderDate,
    LEAD(OrderDate, 1, NULL) OVER (ORDER BY OrderDate) AS NextOrderDate
FROM Orders;

Output

| EmployeeID | OrderDate   | NextOrderDate |
|------------|------------ |-------------- |
| 1          | 2023-05-01  | 2023-05-02    |
| 2          | 2023-05-02  | 2023-05-03    |
| 3          | 2023-05-03  | 2023-05-07    |
| 4          | 2023-05-07  | NULL          |

Explanation

In this example, the LEAD function, with an offset of 1, is used over the OrderDate column - sorted in ascending order. The result is a new column “NextOrderDate” that for each row shows the OrderDate of the subsequent row (lead of 1). Hence, for EmployeeID 2, the NextOrderDate is the OrderDate of EmployeeID 3. The final row has a value of NULL because there is no subsequent row.

Oracle

LEAD(value_expression [, offset] [, default]) OVER ([query_partition_clause] ORDER BY order_by_clause)

• value_expression: This is the column or expression that the `LEAD` function is based on. The `LEAD` function, for a particular row, returns a value from the next row, offset by a specified amount. If the next row does not exist, it returns a default value.
• offset: This parameter allows you to specify the number of rows forward from the current row from which to return a value. If not specified, the default is `1`.
• default: The `default` parameter is the value that will be returned by the function if the offset leads to a row that does not exist. If not specified, the default is `NULL`.
• query_partition_clause: This clause divides the result set produced by the `FROM` clause into partitions to which the `LEAD` function is applied. It is an optional clause. If it is not specified, the function treats all rows of the query result set as a single group.
• order_by_clause: This clause determines the logical order of rows within each partition of the result set. It’s used in combination with the `LEAD` function to specify the order in which the rows are to be ordered when applying the function.

Example

SELECT
  first_name,
  last_name,
  salary,
  LEAD(salary) OVER (ORDER BY salary) as next_higher_salary
FROM employees;

Output

| FIRST_NAME | LAST_NAME | SALARY | NEXT_HIGHER_SALARY |
|------------|-----------|--------|---------------------|
| John       | Doe       | 5000   | 5500                |
| Jane       | Doe       | 5500   | 6000                |
| Dave       | Smith     | 6000   | NULL                |

Explanation

The LEAD function is used to fetch data from a subsequent row to the current row in the specified partition order. In the provided example, the LEAD function returns the salary of the employee who has the next higher salary compared to the current row. If there is no such employee, LEAD returns NULL.