Home » SQL & PL/SQL » SQL & PL/SQL » How can one see what the previous or next row is without leaving the current record
How can one see what the previous or next row is without leaving the current record [message #242830] Tue, 05 June 2007 04:31 Go to next message
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?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG and LEAD functions.

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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.
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 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Complicated Query...Pls help
Next Topic: create table... as select * from ....constraint
Goto Forum:
  


Current Time: Fri Dec 09 11:47:00 CST 2016

Total time taken to generate the page: 0.19396 seconds