# How use LAG Oracle analytic function

- Link: https://ultimasolution.pl/oracle-sql-lag-oracle-11g-is-analytic-function/
- Published: 2026-04-10T09:06:13+02:00
- Author: Karol Preiskorn

Very usefully new features in Oracle 11g is analytic Oracle SQL function `LAG`.

Oracle `LAG()` is an [analytic function](https://www.oracletutorial.com/oracle-analytic-functions/)
that allows you to access the row at a given offset before the current row without
using a [self-join](https://www.oracletutorial.com/oracle-basics/oracle-self-join/).

The following illustrates the syntax of the `LAG()` function:

    ```EnlighterJSRAW
    LAG(expression [, offset ] [, default ]) OVER ( [ query_partition_clause ] order_by_clause )
    ```

## SQL query LAG

This code find change in address post code in journal table:

    ```EnlighterJSRAW
      SELECT  *
        FROM  (
                  SELECT  t1.SITE_NUMBER
                         ,t1.JN_DATETIME
                         ,t1.ADR_POSTCODE
                         ,LAG (t1.ADR_POSTCODE, 1) OVER (PARTITION BY t1.SITE_NUMBER ORDER BY t1.jn_datetime) AS PREV_ADR_POSTCODE
                    FROM  t1 
              )
       WHERE      adr_postcode = '00-001'
              AND PREV_ADR_POSTCODE <> '00-001'
              AND PREV_ADR_POSTCODE <> '00-000'
    ORDER BY  SITE_NUMBER;
    ```

## Test query data

    ```EnlighterJSRAW
    CREATE TABLE T1
    (
      SITE_NUMBER   VARCHAR2(16 BYTE),
      JN_DATETIME   DATE,
      ADR_POSTCODE  VARCHAR2(20 BYTE)
    )
    LOGGING 
    NOCOMPRESS 
    MONITORING;" message="" highlight="" provider="manual"/][pastacode lang="sql" manual="SET DEFINE OFF;
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20001', TO_DATE('08/01/2017 15:01:31', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20002', TO_DATE('04/20/2017 11:59:29', 'MM/DD/YYYY HH24:MI:SS'), '00-002');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20001', TO_DATE('06/15/2017 04:02:41', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20004', TO_DATE('06/20/2017 20:15:13', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20001', TO_DATE('12/10/1929 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20001', TO_DATE('09/25/2091 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20001', TO_DATE('09/14/1914 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20001', TO_DATE('03/25/1911 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
    Insert into KPREISKORN.T1
       (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
     Values
       ('20001', TO_DATE('11/22/2029 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
    COMMIT;
    ```

## SQL result

  |  site_number |  jn_datetime |  adr_postcode |  prev_adr_postcode |  
 |  20001 |  2017-08-01 15:01:31 |  00-001 |  00-003 |

## How it works together

Run online these examples:

 * [http://sqlfiddle.com/#!4/e3f24c/1/0](http://sqlfiddle.com/#!4/e3f24c/1/0)
 * [http://sqlfiddle.com/#!4/e3f24c/1/1](http://sqlfiddle.com/#!4/e3f24c/1/1)