Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

LEAD function in oracle plsql

LEAD Function in Oracle SQL – PLSQL

by

The LEAD function in Oracle SQL / PLSQL is an analytical function and helps us to query more than one row at a time without having self join on the table.
The LEAD function returns the value from the rows ahead in the table.

Syntax for the LEAD Function in Oracle SQL / PLSQL is:
SELECT column(s)
,LEAD (expression [,offset,default]) OVER ([PARTITION BY column(s) ORDER BY column(s)]
FROM table_name(s);

Note:

  • ‘expression’ is an expression that can contain other built in Oracle SQL functions, but cannot contain any Oracle SQL analytica function.
  • ‘offset’ is an optional value. It is a physical offset from the current records in a table, if omitted the default is 1.
  • ‘default’ is also optional. It is the value that would be returned if the offset goes out of bounds of the table, if omitted default will be NULL.

Example 1:

Using LEAD function to without PARTITION BY
Suppose we have a table named ‘orders as shown below:

ORDER_DATE PRODUCT_ID Quantity
25/01/2012 1001 10
26/01/2012 1001 20
27/01/2012 1002 28
28/01/2012 1002 30
29/01/2012 1001 32
30/01/2012 1003 45

If we write our query for LEAD without PARTITION BY as:

SELECT product_id
       ,order_date
       ,LEAD (order_date,1) OVER (ORDER BY order_date) AS Next_Order_Date
FROM orders;

We will get the following output:

PRODUCT_ID ORDER_DATE NEXT_ORDER_DATE
1001 1/25/2012 1/26/2012
1001 1/26/2012 1/27/2012
1002 1/27/2012 1/28/2012
1002 1/28/2012 1/29/2012
1001 1/29/2012 1/30/2012
1003 1/30/2012

Let’s analyze the fetched records for understanding the LEAD Analytical function without PARTITION BY:

  • 1st, 2nd, 3rd, 4th and 5th record tells us the product_id, order_date and also the next_order_date when a product was ordered, it doesn’t matter what product was order, what matters here is that on order was placed on a date.
  • The 6th records tell us that after 1/30/2012 no product was ordered and hence NULL was returned as next_order_date.

Example 2:

Using LEAD function to with PARTITION BY

Suppose we have a table named ‘orders as shown below:

ORDER_DATE PRODUCT_ID Quantity
25/01/2012 1001 10
26/01/2012 1001 20
27/01/2012 1002 28
28/01/2012 1002 30
29/01/2012 1001 32
30/01/2012 1003 45

If we write our query for LEAD with PARTITION BY as:

SELECT product_id
       ,order_date
       ,LEAD (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS Next_Order_Date
FROM orders;

We will get the following output:

PRODUCT_ID ORDER_DATE NEXT_ORDER_DATE
1001 1/25/2012 1/26/2012
1001 1/26/2012 1/29/2012
1001 1/29/2012
1002 1/27/2012 1/28/2012
1002 1/28/2012
1003 1/30/2012

Let’s analyze the fetched records for understanding the LEAD Analytical function with PARTITION BY:

  • 1st, 2nd and 3rd record tells us for the product with product_id = 1001 an order was placed on 01/25/2012 and next order was placed on 01/26/2012 and then the next order was placed on 01/29/2012, it doesn’t matter when the product was ordered, what matters here is that on order was for a particular product was placed.
  • 4th and 5th record tells us that an order for product with product_id = 1002 was placed on 01/27/2012 and the next order for the same was placed on 01/28/2012.
  • The 6th records tell us that after 1/30/2012 product with product_id = 1003 was not ordered and hence NULL was returned as next_order_date.