LAG analytic function in Oracle SQL

lag (expression) over (order-by-clause)
lag (expression, offset) over (order-by-clause)
lag (expression, offset, default-value) over (order-by-clause)

lag (expression) over (query-partition-clause, order-by-clause)

lag is an Analytical function that can be used to get the value of an attribute of the previous row. If you want to retrieve the value of the next row, use lead instead of lag.

The following example will demonstrate this:

A table is created with two attributes: N and M.

set pages 50
set feedback off

create table lag_exp (
  n number,
  m number
);

insert into lag_exp 
  select rownum, mod(rownum * 19 , 13) 
    from all_objects 
   where rownum < 21;

The following select statement will return n and m and additionally a column with the value of M in the previous row:

select n, m, lag(m,1) over (order by n) "Previous M" from lag_exp;

         N          M Previous M
---------- ---------- ----------
         1          6
         2         12          6
         3          5         12
         4         11          5
         5          4         11
         6         10          4
         7          3         10
         8          9          3
         9          2          9
        10          8          2
        11          1          8
        12          7          1
        13          0          7
        14          6          0
        15         12          6
        16          5         12
        17         11          5
        18          4         11
        19         10          4
        20          3         10

Links

On identifiying «periods of activity» is an example that uses lag.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback