I inherited a database that has multiple tables that contain some form of an object ID, a date, and a value. The metrics depend on comparing the current value on any given date to the previous value (and the values do not occur every day). I am looking for a way to quickly fill in a "previous value" field such that for any given record, previous value contains the value from that table where the object ID is the same and the date is the maximum date that occurs before the target row.

Note: I am using Oracle.

Example: {object, date, value}


In this case, the first row should have prev_value set to 22 (third row) because the third row matches on object ID and has the maximum date that occurs before the first row's date.


I am using lag, but it is a bit of a nasty query. I select object_id and lag date over partition on object_id ordered by date. Then, I merge that back into the table to fill the previous date. It takes a very long time, but it appears to work.

  • 1
    Check theLAG() function. Jun 27, 2014 at 11:35
  • I have checked the lag() function. I have to group by object ID because I don't care about events for other objects. All I get is "not a GROUP BY expression" when I use lag().
    – kainaw
    Jun 27, 2014 at 11:55
  • So, show us the command you ran, perhaps there is something wrong with your sql that had LAG and gave error?
    – Raj
    Jun 27, 2014 at 15:25
  • No need for self-join. Do you actually want to UPDATE the table or just show the result? Jun 28, 2014 at 11:01

1 Answer 1


You can use the LAG() function:

    LAG("value", 1) OVER (PARTITION BY "objectID" ORDER BY "date") 
        AS previous_value
    tableX ;

Tested at SQLFiddle

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.