The SQL LEAD function and SQL LAG function can be very useful for finding data in your result set and simplifying your queries. Learn all about them in this article.
Purpose
Both the LEAD and LAG function returns values that are calculated from another row in your result set to the current row. You don’t need to perform a self-join to do this.
The difference is:
- The LEAD function looks at records that appear after the current record
- The LAG function looks at records that appear before the current record
Sounds like they are pretty handy functions!
It also sounds a bit confusing.
Let’s look at the syntax and some examples.
These functions exist in Oracle, SQL Server, MySQL, and Postgres.
LEAD Function Syntax and Parameters
The syntax of the SQL LEAD function is:
LEAD ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )
The parameters of the LEAD function are:
- expression (mandatory): An expression that is calculated and returned for the next rows. Basically, the column or value you want the function to return.
- offset (optional): The number of rows “forward” in the result set to look at. If omitted, the default is 1.
- default (optional): The value to be returned if the offset is outside the bounds of the table. By default, this is NULL.
- query_partition_clause (optional): The partitions to break the analysis into. Just like other analytic functions, it forms a kind of “group”.
- order_by_clause (mandatory): How you want to order the records to determine which row actually comes next, for this function.
Some things to know about this function:
- You can’t use nested analytics functions. So, you can’t use LEAD or any other analytics function inside the expression.
- If you want to look up the previous row instead of the next row, use the LAG function instead of LEAD.
LAG Function Syntax and Parameters
The syntax of the SQL LAG function is:
LAG ( expression [, offset [, default] ] ) OVER ( [ query_partition_clause ] order_by_clause )
The parameters of the LAG function are:
- expression (mandatory): An expression that is calculated and returned for the next rows. Basically, the column or value you want the function to return.
- offset (optional): The number of rows “backward” in the result set to look at. If omitted, the default is 1.
- default (optional): The value to be returned if the offset is outside the bounds of the table. By default, this is NULL.
- query_partition_clause (optional): The partitions to break the analysis into. Just like other analytic functions, it forms a kind of “group”.
- order_by_clause (mandatory): How you want to order the records to determine which row actually comes next, for this function.
Some things you should know about this function:
- You can’t use nested analytics functions. So, you can’t use LAG or any other analytics function inside the expression.
- If you want to look up the next row instead of the previous row, use the LEAD function instead of LAG.
I’ve written a guide to window functions which you can read more about the concept: SQL Window Functions: The Ultimate Guide.
SQL LEAD or LAG Function in the WHERE Clause
Can you use the SQL LEAD function in the WHERE clause? What about the LAG function?
No, you can’t.
If you use the LEAD or LAG function in the WHERE clause, you’ll get an error. In Oracle, the error is:
ORA-30483 error: window functions are not allowed here.
I suspect it has to do with the order that the clauses are calculated, which means you cannot have a WHERE clause that uses the LEAD function or the LAG function.
Examples of the LEAD Function
Here are some examples of the SQL LEAD function.
We’ll use this table output for our examples.
SELECT * FROM student;
Result:
STUDENT_ID | FIRST_ NAME | LAST_ NAME | FEES_ REQUIRED | FEES_ PAID | ENROLMENT_ DATE |
1 | John | Smith | 500 | 100 | 01/02/2015 |
2 | Susan | Johnson | 150 | 150 | 12/01/2015 |
3 | Tom | Capper | 350 | 320 | 06/03/2015 |
4 | Mark | Holloway | 500 | 410 | 20/01/2015 |
5 | Steven | Webber | 100 | 80 | 09/03/2015 |
6 | Julie | Armstrong | 100 | 0 | 12/02/2015 |
7 | Michelle | Randall | 250 | 23/01/2015 | |
8 | Andrew | Cooper | 800 | 400 | 04/03/2015 |
9 | Robert | Pickering | 110 | 100 | 30/01/2015 |
10 | Tanya | Hall | 150 | 150 | 28/01/2015 |
You can run these statements to create and populate the table:
CREATE TABLE student (
student_id NUMBER(4),
first_name VARCHAR2(100),
last_name VARCHAR2(100),
fees_required NUMBER(6),
fees_paid NUMBER(6),
enrolment_date DATE
);
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (1, 'John', 'Smith', 500, 100, TO_DATE('01/02/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (2, 'Susan', 'Johnson', 150, 150, TO_DATE('12/01/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (3, 'Tom', 'Capper', 350, 320, TO_DATE('06/03/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (4, 'Mark', 'Holloway', 500, 410, TO_DATE('20/01/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (5, 'Steven', 'Webber', 100, 80, TO_DATE('09/03/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (6, 'Julie', 'Armstrong', 100, 0, TO_DATE('12/02/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (7, 'Michelle', 'Randall', 250, NULL, TO_DATE('23/01/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (8, 'Andrew', 'Cooper', 800, 400, TO_DATE('04/03/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (9, 'Robert', 'Pickering', 110, 100, TO_DATE('30/01/2015', 'DD/MM/YYYY'));
INSERT INTO student (student_id, first_name, last_name, fees_required, fees_paid, enrolment_date)
VALUES (10, 'Tanya', 'Hall', 150, 150, TO_DATE('28/01/2015', 'DD/MM/YYYY'));
Example 1 – Default Values for LEAD
This example uses the SQL LEAD function with the default values where we can.
SELECT student_id, first_name, last_name, enrolment_date,
LEAD(enrolment_date) OVER (ORDER BY enrolment_date ASC) AS next_enr_date
FROM student
ORDER BY student_id ASC;
Result:
STUDENT_ID | FIRST_NAME | LAST_NAME | ENROLMENT_DATE | NEXT_ENR_DATE |
1 | John | Smith | 01/02/2015 | 12/02/2015 |
2 | Susan | Johnson | 12/01/2015 | 20/01/2015 |
3 | Tom | Capper | 06/03/2015 | 09/03/2015 |
4 | Mark | Holloway | 20/01/2015 | 23/01/2015 |
5 | Steven | Webber | 09/03/2015 | |
6 | Julie | Armstrong | 12/02/2015 | 04/03/2015 |
7 | Michelle | Randall | 23/01/2015 | 28/01/2015 |
8 | Andrew | Cooper | 04/03/2015 | 06/03/2015 |
9 | Robert | Pickering | 30/01/2015 | 01/02/2015 |
10 | Tanya | Hall | 28/01/2015 | 30/01/2015 |
The NEXT_ENR_DATE column shows the result of the LEAD function. It looks for the next row when ordered by enrolment_date, and displays the enrolment_date of that column.
Notice that the order of the output is not the same as the order used in the LEAD function.
Example 2 – Default Values in Order for LEAD
Let’s try the same example, but order the results in the same way as the LEAD function.
SELECT student_id, first_name, last_name, enrolment_date,
LEAD(enrolment_date) OVER (ORDER BY enrolment_date ASC) AS next_enr_date
FROM student
ORDER BY enrolment_date ASC;
Result:
STUDENT_ID | FIRST_NAME | LAST_NAME | ENROLMENT_DATE | NEXT_ENR_DATE |
2 | Susan | Johnson | 12/01/2015 | 20/01/2015 |
4 | Mark | Holloway | 20/01/2015 | 23/01/2015 |
7 | Michelle | Randall | 23/01/2015 | 28/01/2015 |
10 | Tanya | Hall | 28/01/2015 | 30/01/2015 |
9 | Robert | Pickering | 30/01/2015 | 01/02/2015 |
1 | John | Smith | 01/02/2015 | 12/02/2015 |
6 | Julie | Armstrong | 12/02/2015 | 04/03/2015 |
8 | Andrew | Cooper | 04/03/2015 | 06/03/2015 |
3 | Tom | Capper | 06/03/2015 | 09/03/2015 |
5 | Steven | Webber | 09/03/2015 |
The rows returned are the same, as we did not change the LEAD function parameters, but the order of the results is different.
Example 3 – Specifying Values for LEAD
This example shows the LEAD function with some of the values specified.
This query looks up the enrolment date of 2 records in the future and shows a value of 31 Dec 2999 if it is out of bounds
SELECT student_id, first_name, last_name, enrolment_date,
LEAD(enrolment_date, 2, '31-DEC-2999') OVER (ORDER BY enrolment_date ASC) AS next_enr_date
FROM student
ORDER BY enrolment_date ASC;
Result:
STUDENT_ID | FIRST_NAME | LAST_NAME | ENROLMENT_DATE | NEXT_ENR_DATE |
2 | Susan | Johnson | 12/01/2015 | 23/01/2015 |
4 | Mark | Holloway | 20/01/2015 | 28/01/2015 |
7 | Michelle | Randall | 23/01/2015 | 30/01/2015 |
10 | Tanya | Hall | 28/01/2015 | 01/02/2015 |
9 | Robert | Pickering | 30/01/2015 | 12/02/2015 |
1 | John | Smith | 01/02/2015 | 04/03/2015 |
6 | Julie | Armstrong | 12/02/2015 | 06/03/2015 |
8 | Andrew | Cooper | 04/03/2015 | 09/03/2015 |
3 | Tom | Capper | 06/03/2015 | 31/12/1999 |
5 | Steven | Webber | 09/03/2015 | 31/12/1999 |
As you can see, the next_enr_date is from 2 records down the list.
Example 4 – Default Values for LAG
This example uses the SQL LAG function with the default values.
SELECT student_id, first_name, last_name, fees_required,
LAG(fees_required) OVER (ORDER BY fees_required ASC) AS prev_fees_required
FROM student
ORDER BY student_id ASC;
Result:
STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_ REQUIRED | PREV_FEES_ REQUIRED |
1 | John | Smith | 500 | 500 |
2 | Susan | Johnson | 150 | 150 |
3 | Tom | Capper | 350 | 250 |
4 | Mark | Holloway | 500 | 350 |
5 | Steven | Webber | 100 | (null) |
6 | Julie | Armstrong | 100 | 100 |
7 | Michelle | Randall | 250 | 150 |
8 | Andrew | Cooper | 800 | 500 |
9 | Robert | Pickering | 110 | 100 |
10 | Tanya | Hall | 150 | 110 |
The PREV_FEES_REQUIRED column shows the result of the LAG function. It looks for the fees_required from the previous record, when ordered by fees_required.
Notice that the order of the output is not the same as the order of the LAG function.
Example 5 – Default Values in Order for LAG
Let’s use the same example, but the order of the results will be in the same order as the LAG function.
SELECT student_id, first_name, last_name, fees_required,
LAG(fees_required) OVER (ORDER BY fees_required ASC) AS prev_fees_required
FROM student
ORDER BY fees_required ASC;
Result:
STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_ REQUIRED | PREV_FEES_ REQUIRED |
5 | Steven | Webber | 100 | (null) |
6 | Julie | Armstrong | 100 | 100 |
9 | Robert | Pickering | 110 | 100 |
10 | Tanya | Hall | 150 | 110 |
2 | Susan | Johnson | 150 | 150 |
7 | Michelle | Randall | 250 | 150 |
3 | Tom | Capper | 350 | 250 |
4 | Mark | Holloway | 500 | 350 |
1 | John | Smith | 500 | 500 |
8 | Andrew | Cooper | 800 | 500 |
The rows that are returned are the same, but the order of the results is different.
Example 6 – Specifying Values for LAG
This example shows the LAG function with some of the values specified.
It looks up the fees_required of the record 3 rows earlier. If it does not find one because it is out of bounds, it returns 0.
SELECT student_id, first_name, last_name, fees_required,
LAG(fees_required, 3, 0) OVER (ORDER BY fees_required ASC) AS prev_fees_required
FROM student
ORDER BY fees_required ASC;
Result:
STUDENT_ID | FIRST_NAME | LAST_NAME | FEES_REQUIRED | PREV_FEES_REQUIRED |
5 | Steven | Webber | 100 | 0 |
6 | Julie | Armstrong | 100 | 0 |
9 | Robert | Pickering | 110 | 0 |
10 | Tanya | Hall | 150 | 100 |
2 | Susan | Johnson | 150 | 100 |
7 | Michelle | Randall | 250 | 110 |
3 | Tom | Capper | 350 | 150 |
4 | Mark | Holloway | 500 | 150 |
1 | John | Smith | 500 | 250 |
8 | Andrew | Cooper | 800 | 350 |
As you can see, the table shows the PREV_FEES_REQUIRED as a value from 3 records before it.
Example 7: Window Functions
You can use LEAD and LAG as window functions too. Window functions allow you to run the function on a certain subset of the data, called a window. For more information on window functions, read this guide on window functions.
Let’s say you wanted to find the fees paid for the previous student, and the previous student was defined as someone who needed to pay the same fees or less than the current student. But it would be grouped by the required fees.
Here’s what the query would look like:
SELECT student_id, first_name, last_name, fees_required, fees_paid,
LAG(fees_paid) OVER (PARTITION BY fees_required ORDER BY fees_required ASC) AS prev_fees_paid
FROM student
ORDER BY fees_required ASC;
Notice a few things:
- We show both the fees_required and fees_paid
- The LAG function operates on the fees_paid column, which will show the previous record’s fees_paid value
- The PARTITION BY clause indicates how the data is partitioned or grouped for the LAG function only. In this case, it’s into partitions that have the same value for fees_required
- The ordering of data for the LAG function is by fees_required
Here are the results:
STUDENT_ID | FIRST_ NAME | LAST_ NAME | FEES_ REQUIRED | FEES_ PAID | PREV_FEES_PAID |
5 | Steven | Webber | 100 | 80 | |
6 | Julie | Armstrong | 100 | 0 | 80 |
9 | Robert | Pickering | 110 | 100 | |
2 | Susan | Johnson | 150 | 150 | |
10 | Tanya | Hall | >150 | >150 | >150 |
7 | Michelle | Randall | 250 | ||
3 | Tom | Capper | 350 | 320 | |
1 | John | Smith | 500 | 100 | |
4 | Mark | Holloway | 500 | 410 | 100 |
8 | Andrew | Cooper | 800 | 400 |
We can see that the first row has a prev_fees_paid of null as nothing was calculated. The second row has a value of 80, because the row before it has a fees_paid of 80.
The third row has null again because there is no other record that has the same fees_required value.
The fifth row has a value of 150 because row 4 has a fees_paid value of 150.
So that’s how you can use the LAG (and LEAD) function as a window function.
Really understandable. Easy to capture article.
Glad you liked it!
It would be really useful to see some examples of using the partition clause inside of lag/lead window functions. And thank you for the article – it is awesome!
Thanks Igor! Good idea, I can add those to the article.
Thank you for your explanation! It`s all clear now.
Why there are no PARTITION BY clause examples?
BTW, thanks for the article, much better than sample book I’m using right now.
Apologize, I see now, the window function is the partition by clause. Hope for more examples with partition by plus the windowing clause(rows between, preceding and following)
Thanks..