finding difference between two different rows of a column

5 pts.
Tags:
Oracle
Oracle 10g
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following