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.

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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