Ed,
Even in v8 there isn't lag/lead yet in db2 (hopefully they'll add it
tho).
It's not really the same, but you can sorta fudge it with a scalar
subquery:
SQL> select ename,hiredate,
2 lag(ename)over(order by hiredate) previous_ename,
3 lag(hiredate)over(order by hiredate) previous_hiredate
4 from emp where deptno = 10
5 ;
ENAME HIREDATE PREVIOUS_E PREVIOUS_HI
---------- ----------- ---------- -----------
CLARK 09-JUN-1981
KING 17-NOV-1981 CLARK 09-JUN-1981
MILLER 23-JAN-1982 KING 17-NOV-1981
SQL> select e.ename,
2 e.hiredate,
3 (select max(d.ename) from emp d
4 where d.hiredate < e.hiredate
5 and e.deptno = d.deptno) previous_ename,
6 (select max(d.hiredate) from emp d
7 where d.hiredate < e.hiredate
8 and e.deptno = d.deptno) previous_hiredate
9 from emp e
10 where e.deptno = 10;
ENAME HIREDATE PREVIOUS_E PREVIOUS_HI
---------- ----------- ---------- -----------
CLARK 09-JUN-1981
KING 17-NOV-1981 CLARK 09-JUN-1981
MILLER 23-JAN-1982 KING 17-NOV-1981
Good luck,
ant
-----Original Message-----
From: Edwards Ed
[mailto:oracledba-ezmlmshield-x18070832.[Email address protected]
Sent: Wednesday, January 04, 2006 1:26 PM
To: LazyDBA Discussion
Subject: RE: bouncing the db?
Molinaro ("you developer, you"),
That is a "lag" in Oracle but not in DB2 version 7 on the OS/390
(mainframe). Can you send me the code using straight SQL to do the same
thing? Below is the code you send me earlier.
column previous_ename format a15
column previous_hiredate format a18
select ename,hiredate,
lag(ename)over(order by hiredate) previous_ename,
lag(hiredate)over(order by hiredate) previous_hiredate
from emp where deptno = 10
/
Please take a few minutes to provide feedback on the quality of service
you received. The Department of Education values your feedback as a
customer. Commissioner John L. Winn is committed to continuously
assessing and improving the level and quality of services provided to
you by Department staff. Simply click on this link to the "DOE Customer
Survey". Thank you in advance for completing the survey.
DOE Customer Survey
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page