MySQL LEAD Function

Summary: in this tutorial, you will learn how to use the MySQL LEAD() function to access data from the next row in the result set

Introduction to MySQL LEAD() function

The LEAD() function is a window function that allows you to access data from the next row in a result set

Here’s the basic syntax of the LEAD() function:

LEAD(expression, offset, default_value) 
OVER (
  PARTITION BY partition_expression 
  ORDER BY sort_expression
)Code language: SQL (Structured Query Language) (sql)

In this syntax:

expression

This is the column or expression from which you want to retrieve the next value.

offset

The offset specifies the number of rows to look ahead. If you skip it, it defaults to 1, which is the immediate row.

default_value

This is the default value if there is no next row. For example, the last row in the result set (or in a partition) will not have the next row.

If you don’t specify the default_value, it’ll default to NULL.

PARTITION BY partition_expression

The PARTITION BY is an optional clause that divides the result set into partitions to which the LEAD() function is applied independently.

ORDER BY order_expression

The ORDER BY clause specifies the order in which the rows are processed within each partition.

The LEAD() function can be useful for queries like finding the next value in a sequence or calculating the differences between the current and the next values in a column.

MySQL LEAD() function examples

Let’s take some examples of using the LEAD() function. We’ll use the following sales table for the demonstration:

CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);

INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);

SELECT * FROM sales;Code language: PHP (php)

Output:

+----------------+-------------+--------+
| sales_employee | fiscal_year | sale   |
+----------------+-------------+--------+
| Alice          |        2016 | 150.00 |
| Alice          |        2017 | 100.00 |
| Alice          |        2018 | 200.00 |
| Bob            |        2016 | 100.00 |
| Bob            |        2017 | 150.00 |
| Bob            |        2018 | 200.00 |
| John           |        2016 | 200.00 |
| John           |        2017 | 150.00 |
| John           |        2018 | 250.00 |
+----------------+-------------+--------+

1) Basic MySQL LEAD() function example

The following example uses the LEAD() function to pull the sales of the next row into the current row:

SELECT 
  sales_employee,
  fiscal_year, 
  sale, 
  LEAD(sale) OVER (
    PARTITION BY sales_employee
    ORDER BY fiscal_year
  ) AS next_year_sale 
FROM 
  sales;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+-------------+--------+----------------+
| sales_employee | fiscal_year | sale   | next_year_sale |
+----------------+-------------+--------+----------------+
| Alice          |        2016 | 150.00 |         100.00 |
| Alice          |        2017 | 100.00 |         200.00 |
| Alice          |        2018 | 200.00 |           NULL |
| Bob            |        2016 | 100.00 |         150.00 |
| Bob            |        2017 | 150.00 |         200.00 |
| Bob            |        2018 | 200.00 |           NULL |
| John           |        2016 | 200.00 |         150.00 |
| John           |        2017 | 150.00 |         250.00 |
| John           |        2018 | 250.00 |           NULL |
+----------------+-------------+--------+----------------+
9 rows in set (0.00 sec)Code language: plaintext (plaintext)

How it works.

First, the PARTITION BY sales_employee divides the rows in the table into three partitions, each representing the sales of one sales employee.

Second, the ORDER BY fiscal_year sorts the rows in each partition by fiscal year in ascending order.

Therefore, the LEAD() function returns the sales of the next year from the current year for each sales employee. If there is no next year such as for the year 2018, the LEAD() function returns NULL.

2) Using multiple LEAD() functions in a query

The following query uses multiple LEAD() functions to calculate the sales vs. next year’s in percentage:

SELECT 
  sales_employee, 
  fiscal_year, 
  sale, 
  LEAD(sale) OVER (
    PARTITION BY sales_employee 
    ORDER BY fiscal_year
  ) AS next_year_sale,
  ROUND(sale - (LEAD(sale) OVER (
    PARTITION BY sales_employee 
    ORDER BY fiscal_year)) / sale * 100, 2)  'vs_next_year (%)'
FROM 
  sales;Code language: PHP (php)

Output:

+----------------+-------------+--------+----------------+------------------+
| sales_employee | fiscal_year | sale   | next_year_sale | vs_next_year (%) |
+----------------+-------------+--------+----------------+------------------+
| Alice          |        2016 | 150.00 |         100.00 |            83.33 |
| Alice          |        2017 | 100.00 |         200.00 |          -100.00 |
| Alice          |        2018 | 200.00 |           NULL |             NULL |
| Bob            |        2016 | 100.00 |         150.00 |           -50.00 |
| Bob            |        2017 | 150.00 |         200.00 |            16.67 |
| Bob            |        2018 | 200.00 |           NULL |             NULL |
| John           |        2016 | 200.00 |         150.00 |           125.00 |
| John           |        2017 | 150.00 |         250.00 |           -16.67 |
| John           |        2018 | 250.00 |           NULL |             NULL |
+----------------+-------------+--------+----------------+------------------+
9 rows in set (0.00 sec)Code language: PHP (php)

In this example, we added a new column named vs_next_year (%). This column calculates the percentage change from the current year’s sales to the next year’s sales using the formula:

( current year's sale - next year's sale ) x 100 / next year's saleCode language: JavaScript (javascript)

This expression calculates the percentage change and assigns it to the vs_next_year (%) column.

Summary

  • Use the MySQL LEAD() function to retrieve the value from the next row in a specified sequence within a partitioned result set.
Was this tutorial helpful?