Use lag over in inner query : LAG LEAD « Analytical Functions « Oracle PL / SQL

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

Use lag over in inner query

 
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 *
  2        from ( select lag( page_id, 1, null )
  3                        over ( order by seq ) prev,
  4                      page_id,
  5                      lead( page_id, 1, null )
  6                        over ( order by seq ) next
  7                 from pages )
  8       where page_id = 3
  9       /

      PREV    PAGE_ID       NEXT
---------- ---------- ----------
         2          3          4

SQL> drop table pages;

Table dropped.

 

Related examples in the same category