FB

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.

Get Your SQL Cheat Sheet

Three pages of SQL commands to save/print for easy reference

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_IDFIRST_ NAMELAST_ NAMEFEES_ REQUIREDFEES_ PAIDENROLMENT_ DATE
1JohnSmith50010001/02/2015
2SusanJohnson15015012/01/2015
3TomCapper35032006/03/2015
4MarkHolloway50041020/01/2015
5StevenWebber1008009/03/2015
6JulieArmstrong100012/02/2015
7MichelleRandall25023/01/2015
8AndrewCooper80040004/03/2015
9RobertPickering11010030/01/2015
10TanyaHall15015028/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_IDFIRST_NAMELAST_NAMEENROLMENT_DATENEXT_ENR_DATE
1JohnSmith01/02/201512/02/2015
2SusanJohnson12/01/201520/01/2015
3TomCapper06/03/201509/03/2015
4MarkHolloway20/01/201523/01/2015
5StevenWebber09/03/2015
6JulieArmstrong12/02/201504/03/2015
7MichelleRandall23/01/201528/01/2015
8AndrewCooper04/03/201506/03/2015
9RobertPickering30/01/201501/02/2015
10TanyaHall28/01/201530/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_IDFIRST_NAMELAST_NAMEENROLMENT_DATENEXT_ENR_DATE
2SusanJohnson12/01/201520/01/2015
4MarkHolloway20/01/201523/01/2015
7MichelleRandall23/01/201528/01/2015
10TanyaHall28/01/201530/01/2015
9RobertPickering30/01/201501/02/2015
1JohnSmith01/02/201512/02/2015
6JulieArmstrong12/02/201504/03/2015
8AndrewCooper04/03/201506/03/2015
3TomCapper06/03/201509/03/2015
5StevenWebber09/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_IDFIRST_NAMELAST_NAMEENROLMENT_DATENEXT_ENR_DATE
2SusanJohnson12/01/201523/01/2015
4MarkHolloway20/01/201528/01/2015
7MichelleRandall23/01/201530/01/2015
10TanyaHall28/01/201501/02/2015
9RobertPickering30/01/201512/02/2015
1JohnSmith01/02/201504/03/2015
6JulieArmstrong12/02/201506/03/2015
8AndrewCooper04/03/201509/03/2015
3TomCapper06/03/201531/12/1999
5StevenWebber09/03/201531/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_IDFIRST_NAMELAST_NAMEFEES_ REQUIREDPREV_FEES_ REQUIRED
1JohnSmith500500
2SusanJohnson150150
3TomCapper350250
4MarkHolloway500350
5StevenWebber100(null)
6JulieArmstrong100100
7MichelleRandall250150
8AndrewCooper800500
9RobertPickering110100
10TanyaHall150110

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_IDFIRST_NAMELAST_NAMEFEES_ REQUIREDPREV_FEES_ REQUIRED
5StevenWebber100(null)
6JulieArmstrong100100
9RobertPickering110100
10TanyaHall150110
2SusanJohnson150150
7MichelleRandall250150
3TomCapper350250
4MarkHolloway500350
1JohnSmith500500
8AndrewCooper800500

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_IDFIRST_NAMELAST_NAMEFEES_REQUIREDPREV_FEES_REQUIRED
5StevenWebber1000
6JulieArmstrong1000
9RobertPickering1100
10TanyaHall150100
2SusanJohnson150100
7MichelleRandall250110
3TomCapper350150
4MarkHolloway500150
1JohnSmith500250
8AndrewCooper800350

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!

Get Your SQL Cheat Sheets Now: