Re: Next and Previos Row in the Table

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Wed, 12 Jun 2002 21:27:26 +0100
Message-ID: <3d07aeb7$0$8510$cc9e4d1f@news.dial.pipex.com>

"Roman Mirzaitov" <rmirzaitov_at_kt.kg> wrote in message news:ae6g4a$4bgtm$1_at_ID-127142.news.dfncis.de...
> Hello Alex,
>
> thank you for your invaluable advices very much. I'll take them in mind
> certainly.
> But now calm down and reread original post. As you can see there is
definite
> criterion of "previous" and "next" record (as you said "This order is
> defined by you"). And I just reply to the answer, no more no less.

There isn't. munda asked for next and previous in the table. here is what you could get.

SQL> ed
Wrote file afiedt.buf

  1* create table emp(employee_id number,employee_name varchar2(50))   2 /

Table created.

SQL> insert into emp values(111,'aaa');

1 row created.

SQL> insert into emp values(333,'ccc');

1 row created.

SQL> insert into emp values(888,'hhh');

1 row created.

SQL> insert into emp values(222,'bbb');

1 row created.

SQL> insert into emp values(444,'ddd');

1 row created.

SQL> insert into emp values(555,'eee');

1 row created.

SQL> insert into emp values(999,'cop');

1 row created.

SQL> insert into emp values(777,'ggg');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

EMPLOYEE_ID EMPLOYEE_NAME

----------- --------------------------------------------------
        111 aaa
        333 ccc
        888 hhh
        222 bbb
        444 ddd
        555 eee
        999 cop
        777 ggg

8 rows selected.

which records are next and previous. your query will return 999 and 777 The only way to tell which is next and which previous is by inspection. .

SQL> ed
Wrote file afiedt.buf

  1 select * from emp
  2 where EMPLOYEE_ID in (
  3 888,
  4 (select min(EMPLOYEE_ID) from emp where EMPLOYEE_ID>888),   5 (select max(EMPLOYEE_ID) from emp where EMPLOYEE_ID<888)   6* )
SQL> / EMPLOYEE_ID EMPLOYEE_NAME

----------- --------------------------------------------------
        888 hhh
        999 cop
        777 ggg

> Moreover I'm afraid that LEAD and LAG don't appropriate solution for
Munda's
> problem.
> Thanks again.

but they do

SQL> select * from (
  2 SELECT employee_id,employee_name,

  3     LEAD(employee_id, 1) OVER (ORDER BY employee_id) AS "Next",
  4     Lag(employee_id, 1) OVER (ORDER BY employee_id) AS "previous"

  5 FROM emp
  6 )
  7 where employee_id = 888;

EMPLOYEE_ID EMPLOYEE_NAME                        Next   previous
----------- ------------------------------ ---------- ----------
        888 V_$SORT_USAGE                         889        887

assuming you want to order by employee id. 1 row returned with next and previous clearly marked as such. Moreover you can easily throw this into an SP for calling from a web page a typical application of paging. Received on Wed Jun 12 2002 - 15:27:26 CDT