Lag over order : LAG LEAD « Analytical Functions « Oracle PL / SQL






Lag over order

 
SQL>
SQL>      create table pages(
  2        page_id number,
  3        seq     number )
  4      /

Table created.

SQL>      insert into pages values ( 1, 10 );

1 row created.

SQL>      insert into pages values ( 2, 20 );

1 row created.

SQL>      insert into pages values ( 3, 30 );

1 row created.

SQL>      insert into pages values ( 4, 40 );

1 row created.

SQL>      commit;

Commit complete.

SQL>
SQL>       select lag( page_id, 1, null )
  2               over ( order by seq ) prev,
  3             page_id,
  4             lead( page_id, 1, null )
  5               over ( order by seq ) next
  6        from pages
  7       where page_id = 3
  8      /

      PREV    PAGE_ID       NEXT
---------- ---------- ----------
                    3

SQL>
SQL> drop table pages;

Table dropped.

 








Related examples in the same category

1.LAG() and LEAD(): get a value in a row where that row is a certain number of rows away from the current row
2.The Row Comparison Functions LEAD and LAG
3.The row comparison function can also be partitioned as with other aggregates
4.LAG, LEAD: allow specified offsets and default values for the nulls that result in non-applicable rows
5.lead over order by
6.Lag over
7.Lead over partition
8.Use lag over in inner query
9.Use lag over and lead over in inner statement