É possível usar funções analíticas pra buscar a linha anterior.
Por exemplo: (neste caso, agrupando por departamento)
Selecionar tudo
select hiredate
, LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename)
From EMP;
Selecionar tudo
DEPTNO ENAME HIREDATE LAG(HIREDATE,1,NULL)OVER(PARTI
------ ---------- ----------- ------------------------------
10 CLARK 6/9/1981
10 KING 11/17/1981 6/9/1981
10 MILLER 1/23/1982 11/17/1981
20 SMITH 12/17/1980
20 JONES 4/2/1981 12/17/1980
20 FORD 12/3/1981 4/2/1981
20 SCOTT 4/19/1987 12/3/1981
20 ADAMS 5/23/1987 4/19/1987
30 ALLEN 2/20/1981
30 WARD 2/22/1981 2/20/1981
30 BLAKE 5/1/1981 2/22/1981
30 TURNER 9/8/1981 5/1/1981
30 MARTIN 9/28/1981 9/8/1981
30 JAMES 12/3/1981 9/28/1981
14 rows selected
Ou também tem como fazer a tabela toda: (sem agrupamento)
Selecionar tudo
select hiredate
, LAG(hiredate,1,NULL)
OVER (PARTITION BY NULL
ORDER BY hiredate, ename)
From EMP;
Selecionar tudo
HIREDATE LAG(HIREDATE,1,NULL)OVER(PARTI
----------- ------------------------------
12/17/1980
2/20/1981 12/17/1980
2/22/1981 2/20/1981
4/2/1981 2/22/1981
5/1/1981 4/2/1981
6/9/1981 5/1/1981
9/8/1981 6/9/1981
9/28/1981 9/8/1981
11/17/1981 9/28/1981
12/3/1981 11/17/1981
12/3/1981 12/3/1981
1/23/1982 12/3/1981
4/19/1987 1/23/1982
5/23/1987 4/19/1987
Veja esse texto com diversos exemplos de funções analíticas:
https://glufke.net/oracle/download/funco ... TICAS.html