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.
Best Answer
Analytics functions to the rescue
In particular, lead/lag will be your friend.
SQLFiddle