Use lag over and lead over in inner statement : LAG LEAD « Analytical Functions « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Analytical Functions
  3. LAG LEAD

Use lag over and lead over in inner statement

 
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>      insert into pages values ( 5, 25 );

1 row created.

SQL>      select * from ( 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
---------- ---------- ----------
         5          3          4

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.Lag over order
9.Use lag over in inner query