• Skip to primary navigation
  • Skip to main content

Tech Honey

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

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / LAG Function in Oracle SQL – PLSQL

LAG Function in Oracle SQL – PLSQL

November 20, 2012 by techhoneyadmin

The LAG 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 LAG function returns the value from the rows prior in the table.

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

  • ‘expression’ is an expression that can contain other built in Oracle SQL functions, but cannot contain any Oracle SQL analytical 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 LAG 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 LAG without PARTITION BY as:

 SELECT product_id
       ,order_date
       ,LAG(order_date,1) OVER (ORDER BY order_date) AS Previous_Order_Date
FROM orders;

We will get the following output:

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

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

  • 2nd, 3rd, 4th, 5th, and 6th record tells us the product_id, order_date and also the previous_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 1st records tell us that before 1/25/2012 no product was ordered and hence NULL was returned as previous_order_date.

Example 2:

Using LAG 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 LAG with PARTITION BY as:

SELECT product_id
       ,order_date
       ,LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS PREVIOUS_Order_Date
FROM orders;

We will get the following output:

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

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

  • 2nd and 3rd record tells us that the product with product_id = 1001 was ordered on 01/29/2012 and previous order was placed on 01/26/2012 and then also an order was placed on 01/25/2012 but before 01/25/2012 no order was placed and hence NULL is returned.
  • 4th and 5th record tells us that an order for product with product_id = 1002 was placed on 01/28/2012 and the previous order for the same was placed on 01/27/2012 and no previous order was placed.
  • The 6th records tell us that before 1/30/2012 product with product_id = 1003 was not ordered and hence NULL was returned as previous_order_date.

Filed Under: function Tagged With: how to use LAG function in oracle database query, how to use LAG function in oracle plsql, how to use LAG function in oracle sql, LAG function in oracle plsql, LAG function in oracle sql, LAGPLSQL, syntax and example of LAG function in oracle database query, syntax and example of LAG function in oracle plsql, syntax and example of LAG function in oracle sql, using LAG function in oracle database query, using LAG function in oracle plsql, using LAG function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in