If your interested to learn about the MySQL UUID
The LEAD and LAG is a window function in MySQL used to access the preceding and succeeding value of specified rows from the current row within its partition. These functions are the kind of non-aggregate function. The Window functions in MySQL are used to perform operations or calculations for each row within the partition or window. These functions produce the result similar to a calculation done by using the aggregate functions. But, unlike aggregate functions that perform operations on an entire table, window functions do not produce a result to be grouped into a single row. Therefore each row maintains the unique identities. In the window function, we must know about these things:
- The row on which function evaluation occurs is termed as the current row.
- A window is the set of rows related to the current row or using which function operates on that row.
Let us learn about these functions in detail.
MySQL LEAD Function
This function allows us to look forward rows or succeeding rows to get/access the value of that row from the current row. It is a very useful method to calculate the difference between the current and subsequent rows within the same output. Below is the general syntax to use the LEAD function in MySQL:
LEAD(expression, offset , default_value) OVER ( PARTITION BY (expr) ORDER BY (expr) )
Parameter Explanation
The LEAD function syntax contains the following parameters.
Parameter | Descriptions |
---|---|
expression | It is a column name or any built-in function whose value return by the function. |
offset | It contains the number of rows succeeding from the current row. It should be a positive integer value. If it is zero, the function evaluates the result for the current row. If we omit this, the function uses 1 by default. |
default_value | It is a value that will return when we have no subsequent row from the current row. If we omit this, the function returns the null value. |
OVER | OVER It is responsible for partitioning rows into groups. If it is empty, the function performs an operation using all rows. |
PARTITION BY | It split the rows in the result set into partitions to which a function is applied. If we have not specified this clause, all rows treated as a single row in the result set. |
ORDER BY | It determines the sequence of rows in the partitions before the function is applied. |
MySQL LEAD() Function Example
Here, we are going to understand how the LEAD function works with the MySQL table. First, we need to create a table named sales_table using the below statement.
CREATE TABLE sales_table ( Employee_Name VARCHAR(45) NOT NULL, Year INT NOT NULL, Country VARCHAR(45) NOT NULL, Product VARCHAR(45) NOT NULL, Sale DECIMAL(12,2) NOT NULL, PRIMARY KEY(Employee_Name, Year) );
Next, we will add records into this table using the INSERT statement as follows:
INSERT INTO sales_table VALUES ('Stephen', 2017, 'India', 'Laptop', 10000), ('Stephen', 2018, 'India', 'Laptop', 15000), ('Stephen', 2019, 'India', 'TV', 20000), ('Bob', 2017, 'US', 'Computer', 15000), ('Bob', 2018, 'US', 'Computer', 10000), ('Bob', 2019, 'US', 'TV', 20000), ('Mandy', 2017, 'Canada', 'Mobile', 20000), ('Mandy', 2018, 'Canada', 'Calculator', 1500), ('Mandy', 2019, 'Canada', 'Mobile', 25000);
We can verify the records into a table using the SELECT statement. It will give the output as below:

The following statement finds the sale and next sales detail of each employee:
SELECT Year, Product, Sale, LEAD(Sale,1) OVER ( PARTITION BY Year ORDER BY Country) AS Next_Sale FROM sales_table;
This example first divided the result set by the year into partitions and then sorted each partition using the country column. Finally, we applied the LEAD() function on each partition to get the next sales detail. The below output explains it more clearly:

In the output, we can see the null value in each partition. When the succeeding row crosses the partition boundary, the next value in each partition’s last row always becomes NULL.
Lead function with a default value
Let’s specify a default value for the argument default and rerun the query.
1234 | SELECT *, LEAD(JoiningDate, 1,0) OVER( ORDER BY JoiningDate ASC) AS EndDateFROM @Employee; |
We get an error message – Operand type clash: int is incompatible with date. In the table variable, we have the JoiningDate column defined as Date. We also applied the SQL Server Lead function on the date data type. We cannot use an integer data type value for the date data type. Due to this, we get this error message of the incompatible data type. Let’s specify the default date in this argument and rerun the query.
1234 | SELECT *, LEAD(JoiningDate, 1,’2018-01-01′) OVER( ORDER BY JoiningDate ASC) AS EndDateFROM @Employee; |
In this output, we get the specified default date instead of the NULL value.

MySQL LAG Function
This function allows us to look information about backword rows or preceding rows to get/access the value of a previous row from the current row. It is a very useful method to calculate the difference between the current and the previous row within the same result set. MySQL LEAD and LAG Function Below is the general syntax to use the LAG function in MySQL:
LAG (expression, offset , default_value) OVER ( PARTITION BY (expr) ORDER BY (expr [ASC|DESC]) )
Parameter Explanation
The LAG function syntax contains the following parameters.
Parameter | Descriptions |
---|---|
expression | It is a column name or any built-in function. |
offset | It contains the number of rows preceding from the current row. It should be zero or any positive integer value. If it is zero, the function evaluates the result for the current row. If we omit this, the function uses 1 by default. |
default_value | It is a value that will return when we have no preceding row from the current row. If we omit this, the function returns the null value. |
The other parameters such as OVER, PARTITION BY, ORDER BY clause meaning are the same as the LEAD function.
MySQL LAG() Function Example
Here, we are going to understand how the LAG function works with the MySQL table. We can use the above table named sales_table . The following statement finds the sale and previous sales detail of each employee:
SELECT Year, Product, Sale, LAG(Sale, 1, 0) OVER ( PARTITION BY Year ORDER BY Country) AS Previous_Sale_LAG FROM sales_table;
This example first divided the result set by the year into partitions and then sorted each partition using the country column. Finally, we applied the LAG() function on each partition to get the previous sales detail. After execution of the above statement, we can see the below output:

In the output, we can see the 0.00 value in each partition. It indicates the value of the row that will not exist in a table. If we have not provided the default value, it will become NULL.
MySQL LAG()
function example
We will use the orders
, orderDetails
, and productLines
tables from the the sample database for the demonstration.
The following statement returns the order value of every product line in a specific year and the previous year:
<code>WITH productline_sales AS ( SELECT productline, YEAR(orderDate) order_year, ROUND(SUM(quantityOrdered * priceEach),0) order_value FROM orders INNER JOIN orderdetails USING (orderNumber) INNER JOIN products USING (productCode) GROUP BY productline, order_year ) SELECT productline, order_year, order_value, LAG(order_value, 1) OVER ( PARTITION BY productLine ORDER BY order_year ) prev_year_order_value FROM productline_sales; </code><small>Code language: SQL (Structured Query Language) (sql)</small>
Here is the output:

In this example:
- First, we used a common table expression to get the order value of every product in every year.
- Then, we divided the products using the product lines into partitions, sorted each partition by order year, and applied the
LAG()
function to each sorted partition to get the previous year’s order value of each product.
Note that we used the ROUND()
function to round the order values to zero decimal places.
In this tutorial, you have learned how to use the MySQL LAG()
function to access data of the previous row from the current row.