title: Retrieving a current and most recent previous value (Oracle)
body: <p>I'm facing a problem which I've spent a great deal of time trying to address, and although I have a solution, it's clunky and involves pl/sql processing, and I was wondering what others might come up with.</p>
  
  <p>I'm working with a dataset that creates a new row every time a record is changed, thus maintaining a history. The most up-to-date version is then displayed in our application. Consider a table with the following data:</p>
  
  <pre>
  Person ID  Last_Name  Address_line1       Effective_Start_Date  Effective_End_Date
  4913       Jones      1 First Street      03-aug-02             31-dec-12
  4913       Cross      1 First Street      01-feb-02             02-aug-02
  4913       Cross      86 Green Avenue     01-mar-01             31-jan-02
  4913       Cross      87 Devonshire Road  01-jan-90             28-feb-02
  </pre>
  
  <p>As part of a report, I need to extract the details which have changed between a given set of dates. For instance, say I want to extract the current <code>address_line1</code> and the previous <code>address_line1</code> along with the date of change (<code>effective_start_date</code> when the new address was added). The caveat is that if other column data changes, this will create a new row too. For instance, in the example above, the <code>last_name</code> changed after the address changed. </p>
  
  <p>Unfortunatey, the query must be generic so that it can be run as part of a report,i.e. not having to specify explicitly the effective start and end dates. </p>
  
  <p>Hope that all makes sense. Hopefully, you're all still with me. So, given the data-set above, I would expect to see the following results in my report:</p>
  
  <pre>
  Person ID  Surname  Address_line1   Prev_Address_line1  Effective Start date of New Address Line 1
  4913       Jones    1 First Street  86 Green Avenue     01-feb-02
  </pre>
  
  <p>My approach involves processing with pl/sql and looping over a considerable number of records but I was wondering if this can be done in a single sql query.</p>
  
  <p>Does anyone have any ideas on whether this can be done using only sql?</p>