SQL LEAD and LAG Function Guide, FAQ, and Examples

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.

 

You can find a full list of Oracle SQL functions here.