title: SQL query to compare current and previous value in a column in a history table
body: <p>I have a SQL Server 2008 database with a history table for recording changes in a main table. I need to report the current (latest) value of the 'rate' column, and the most recent previous value that's different from current.</p>
  
  <p>So, given something like:</p>
  
  <pre><code>id   | rate   | uninteresting | updated_on   | version
  -----+--------+---------------+--------------+----------
  123  | 1.20   | foo           | 2010-10-18   | 1500
  456  | 2.10   | bar           | 2010-10-12   | 2123
  123  | 1.20   | baz           | 2010-10-10   | 1499
  123  | 1.10   | baz           | 2010-10-08   | 1498
  456  | 2.00   | bar           | 2010-10-11   | 2122
  123  | 1.00   | baz           | 2010-08-01   | 1497
  456  | 2.00   | quux          | 2010-10-05   | 2121
  456  | 1.95   | quux          | 2010-09-07   | 2120
  </code></pre>
  
  <p>I want to produce:</p>
  
  <pre><code>id   | cur_rate | cur_ver | updated_on | prev_rate | prev_ver | prev_updated
  -----+----------+---------+------------+-----------+----------+-------------
  123  | 1.20     | 1500    | 2010-10-18 | 1.10      | 1498     | 2010-10-08
  456  | 2.10     | 2123    | 2010-10-12 | 2.00      | 2122     | 2010-10-11
  </code></pre>
  
  <p>Note that I'm looking for the latest entry where the rate is different from the most recent entry.</p>
  
  <p>I've tried various approaches, but either get way too many results, or none at all. Any suggestions?</p>