Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Next and Previos Row in the Table
"Roman Mirzaitov" <rmirzaitov_at_kt.kg> wrote in message news:<ae3p00$3fon5$1_at_ID-127142.news.dfncis.de>...
> Hi,
>
> select * from emp_table
> where EMPLOYEE_ID in (
> 888,
> (select min(EMPLOYEE_ID) from a where EMPLOYEE_ID>888),
> (select max(EMPLOYEE_ID) from a where EMPLOYEE_ID<888)
> )
>
> Regards,
> --
> Roman Mirzaitov
> Brainbench MVP for Oracle Administration
> www.brainbench.com
>
>
> "Munda" <munda_73_at_yahoo.com> wrote in message
> news:c2b0e26b.0206101233.67d75be5_at_posting.google.com...
> > I have a employee table as following
> > EMPLOYEE_ID Number
> > EMPLOYEE_NAME VARCHAR2(20)
> >
> > For example consider table has following data
> >
> > EMPLOYEE_ID EMPLOYEE_NAME
> > 111 a
> > 333 c
> > 555 e
> > 666 f
> > 888 h
> > 999 i
> >
> >
> > I want to display Previous and Next record for the specific Employee .
> > For Example
> > If I the specified employee id is 888 then I will need employee 666 and
> 999
> > I want these in single SQL .
> > Please email me back @ munda_73_at_yahoo.com
> > Help will be greatly appreciated
There is no Previous and Next records in Oracle tables! There is no order whatsoever! Please read documentation, guys! You don't need to read everything, but you have to read Concepts and SQL Language Reference, at least. And you need to know the basics of Relational theory.
Now, back to the question. Obviously, you need next and previous record based on some order. This order is defined by you, this is not the order in the table. This can be done using analytic functions, LEAD and LAG in this case. Received on Tue Jun 11 2002 - 11:36:03 CDT