2

Consider an Oracle database that has a person, a date and a value in every record. When I pull a record, I want to very quickly and easily get the previous record where person is the same in both the one I fetched and the previous and date is the maximum date that comes before the record I fetched. When I ask for this, there is usually a hell of a lot of confusion, so here is an example data set:

PERSON,DATE,VALUE
1,2013-12-25,823
2,2013-11-20,384
1,2013-11-10,4837
1,2013-10-01 377
1,2014-01-01,8347

Now, when I fetch the first record, I want to also get the record 1,2013-11-10,4837 because person matches (as 1, not 2), and the date of the previous record is the maximum date that comes before the record I fetched.

I have tried using the connect clause, but it doesn't give the ability to ask for "the record with the maximum date that is less than the date of the currect record." The multiple join method that I'm using isn't functional because there are too many records - it takes hours to complete a single query.

Improve this question

1 Answer 1

Reset to default
5

Analytics functions to the rescue

In particular, lead/lag will be your friend.

SELECT
   LAG(ACTION_DATE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC)  PREVIOUS_ACTION_DATE
,  LAG(VALUE) OVER (PARTITION BY PERSON ORDER BY ACTION_DATE ASC)  PREVIOUS_VALUE
,  PERSON
,  ACTION_DATE
,  VALUE
FROM
    SomeTabe

SQLFiddle

Improve this answer
2
  • Was just about to post mine using LEAD() and DESC :) haha.
    – Philᵀᴹ
    Commented Dec 9, 2013 at 14:15
  • Thanks. I haven't used lead or lag before. Trying it now.
    – kainaw
    Commented Dec 9, 2013 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.