SQL: retrieve only the records whose value has changed

Question

Sorry for the nondescript title. I'll edit as we go along.

I have a table RateTable:

| Code   |  Date     |   Rate  |

  B001     2009-01-01   1.05
  B001     2009-01-02   1.05
  B001     2009-01-03   1.05
  B001     2009-01-04   1.05
  B001     2009-01-05   1.06
  B001     2009-01-06   1.06
  B001     2009-01-07   1.06
  B001     2009-01-08   1.07

There is an entry for each day, but the Rate rarely changes. Can I write a SQL query that will only return the rows in which a Rate change occurs? I'm using SQLServer

Answer 1

If I read this right, you aren't looking for modified rows, but rows where the rate changes from the previous date. This query or something like it should do it:

SELECT  r1.Code, r1.Date, r1.Rate
FROM    RateTable r1
WHERE   r1.Rate <> (SELECT TOP 1 Rate
                   FROM    RateTable
                   WHERE   Date < r1.Date
                   ORDER BY Date DESC)