FB

Oracle LEAD and LAG Function Guide, FAQ, and Examples

The Oracle LEAD function and Oracle LAG function can be very useful at 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 that the LEAD function looks in records that appear after the current record, and LAG looks in 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.

 

LEAD Function Syntax and Parameters

The syntax of the Oracle 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 Oracle 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.

Oracle LEAD or LAG Function in the WHERE Clause

Can you use the Oracle 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 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 Oracle LEAD function. I find that examples are the best way for me to learn about code, even with the explanation above.

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 Oracle 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 Oracle 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 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.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

7 thoughts on “Oracle LEAD and LAG Function Guide, FAQ, and Examples”

  1. 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!

  2. Why there are no PARTITION BY clause examples?
    BTW, thanks for the article, much better than sample book I’m using right now.

  3. 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..

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.