finding difference between two different rows of a column
Hi aLL,
I have a database running oracle 10g. I want to know if there is an oracle function that takes care of difference between two values in two different rows of thesame column.
Thanks
Soga
Software/Hardware used:
Software/Hardware used:
ASKED:
December 5, 2007 11:41 AM
UPDATED:
December 6, 2007 4:33 PM
Answer Wiki:
Hi, Soga,
That depends on what you mean by "takes care of difference".
Many Oracle <i><b>analytic functions</b></i> (including LAG and LEAD) can reference a value from another row of the same result set, as in the following example that shows the the difference (in days) between the date each employee was hired and the previous hiring in the same department:
<pre>
SELECT deptno
, ename
, hiredate
, hiredate - LAG (hiredate) OVER
( PARTITION BY deptno
ORDER BY hiredate
) AS dif_hiredate
FROM scott.emp
ORDER BY deptno
, hiredate;
DEPTNO ENAME HIREDATE DIF_HIREDATE
---------- ---------- ----------- ------------
10 CLARK 09-Jun-1981
10 KING 17-Nov-1981 161
10 MILLER 23-Jan-1982 67
20 SMITH 17-Dec-1980
20 JONES 02-Apr-1981 106
20 FORD 03-Dec-1981 245
20 SCOTT 19-Apr-1987 1963
20 ADAMS 23-May-1987 34
30 ALLEN 20-Feb-1981
30 WARD 22-Feb-1981 2
30 BLAKE 01-May-1981 68
30 TURNER 08-Sep-1981 130
30 MARTIN 28-Sep-1981 20
30 JAMES 03-Dec-1981 66
14 rows selected.
</pre>
If analytic functions don't do what you want, maybe a self-join or MODEL will.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
