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.

 

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

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.

 

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!

Improve Your Oracle SQL With My 10-Day Email Course

x