OraFAQ Forum: SQL & PL/SQL » How can one see what the previous or next row is without leaving the current record

Home » SQL & PL/SQL » SQL & PL/SQL » How can one see what the previous or next row is without leaving the current record

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How can one see what the previous or next row is without leaving the current record [message #242830]

Tue, 05 June 2007 04:31

haiza
Messages: 22
Registered: June 2007

Junior Member

I'm trying to compare the value with the current record against
the next or previous value. I want to do this for all the rows in the table.
I have been using the For Loop to go through each row but don't know how to get the previous/next row to compare.

See below for my loop syntax

For r_c1 in c1 loop

Loop

Can anyone help?

Report message to a moderator

Re: How can one see what the previous or next row is without leaving the current record [message #242832 is a reply to message #242830]

Tue, 05 June 2007 04:33

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Have a look at LAG and LEAD functions.

Regards
Michel

Report message to a moderator

Re: How can one see what the previous or next row is without leaving the current record [message #242835 is a reply to message #242830]

Tue, 05 June 2007 04:40

mbalves
Messages: 20
Registered: May 2007

Junior Member

Hello,

Why you don't keep the two before rows?

....
r_c1Bef1 := null;
rc1_Bef2 := null;
For r_c1 in c1 loop
if rc1_Bef1 is not null and rc2_Bef2 is not null then
....
end if;
rc1_Bef2 := rc1_Bef1;
rc1_Bef1 := rc1;
Loop

MBA

Report message to a moderator

Re: How can one see what the previous or next row is without leaving the current record [message #242836 is a reply to message #242832]

Tue, 05 June 2007 04:43

haiza
Messages: 22
Registered: June 2007

Junior Member

I have but don't really understand the Lead and Lag function. i have tried the Lead function but didn't get the result that I wanted

This is my SQL statement which is in the cursor c1

select STREAM,to_char( PROCESSED_DATE,'hh24') hour,to_char( PROCESSED_DATE,'d') day, NO_CDR from xacct_cdrs
WHERE substr(stream, length(stream),1) = 'I'
order by 1,3,2,4;

I'm trying to compare the hour field.

Report message to a moderator

Re: How can one see what the previous or next row is without leaving the current record [message #242848 is a reply to message #242836]

Tue, 05 June 2007 05:13

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> select ename, hiredate,
  2         lag(ename) over(order by hiredate) prev_name,
  3         lag(hiredate) over(order by hiredate) prev_hiredate,
  4         lead(ename) over(order by hiredate) next_name,
  5         lead(hiredate) over(order by hiredate) next_hiredate
  6  from emp
  7  order by hiredate
  8  /
ENAME      HIREDATE    PREV_NAME  PREV_HIREDA NEXT_NAME  NEXT_HIREDA
---------- ----------- ---------- ----------- ---------- -----------
SCOTT      19-APR-0087                        ADAMS      23-MAY-0087
ADAMS      23-MAY-0087 SCOTT      19-APR-0087 SMITH      17-DEC-1980
SMITH      17-DEC-1980 ADAMS      23-MAY-0087 ALLEN      20-FEB-1981
ALLEN      20-FEB-1981 SMITH      17-DEC-1980 WARD       22-FEB-1981
WARD       22-FEB-1981 ALLEN      20-FEB-1981 JONES      02-APR-1981
JONES      02-APR-1981 WARD       22-FEB-1981 BLAKE      01-MAY-1981
BLAKE      01-MAY-1981 JONES      02-APR-1981 CLARK      09-JUN-1981
CLARK      09-JUN-1981 BLAKE      01-MAY-1981 TURNER     08-SEP-1981
TURNER     08-SEP-1981 CLARK      09-JUN-1981 MARTIN     28-SEP-1981
MARTIN     28-SEP-1981 TURNER     08-SEP-1981 KING       17-NOV-1981
KING       17-NOV-1981 MARTIN     28-SEP-1981 JAMES      03-DEC-1981
JAMES      03-DEC-1981 KING       17-NOV-1981 FORD       03-DEC-1981
FORD       03-DEC-1981 JAMES      03-DEC-1981 MILLER     23-JAN-1982
MILLER     23-JAN-1982 FORD       03-DEC-1981

14 rows selected.

Regards
Michel

Report message to a moderator

Previous Topic:	Complicated Query...Pls help
Next Topic:	create table... as select * from ....constraint

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 26 19:53:43 CST 2024