SQL: retrieve only the records whose value has changed

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

From stackoverflow

• Add a new column which will be a DateTime and track when the rate changed. Then you can just return any columns who DateModified >= x...Where x is the last time you checked.

• I would add [ModifiedDate] column to your table and update it's value each time new record is inserted or updated. Then you will be able to get data from the table based on that column:

  SELECT * FROM [YourTable] WHERE [ModifiedDate] > '2008-01-20 10:20'
  

• If you add a new column "LastChangedDate" and you track the last read in an other table (ie. LastReadedValues), than you can easly track the changes.

• 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)
  

  JoshBerke : Ohh well I totally missed his intent on the question. But looking at his sample data I think your right

  David M : I read it a couple of times - if your reading was right then so was your solution of course...

  Mike Sickler : fantastic- thanks!

• I would advise, if you have control over this at this point, to only right bookended data. In other words, only write a record to the table when the rate changes. You can then assume that any data between the changes will have stayed the same. This will greatly reduce the amount of data you need to store.

  That said, this query, or something close, aught to accomplish what you're asking for:

  select rt.Code, MIN(rt.Date), rt.Rate
  from RateTable rt
  group by rt.Code, rt.Rate
  

  edit: sorry, change max to min

  David M : But if the rate was on 1.05, goes up to 1.06, then back down to 1.05, then you lose the first period with that query.

  dustyburwell : oh, criminey. You're absolutely right. Well...Ignore my SQL, but I'd still take the advice before it.

• how about this approach guys?

  add a bit column named ChangedSinceLastRead, set it to 1 whenever you change the value in this table and in your select query read "SELECT * FROM Rate WHERE ChangedSinceLastRead = 1"

  after this select query fire another query "UPDATE Rate SET ChangedSinceLastRead = 0"

  although you have to fire 1 additional update query on this, you dont have to compute against dates in your select and your table storage takes less space (as bit is smaller to datetime)

  just another way to solve this ;-) i would love to see the performance implications of this as well as the above suggested datetime approach

• If your RDBMS supports analytic functions then the optimum method is almost certainly this:

  select code, date, rate, last_rate
  from
  (
  select code,
         date,
         rate,
         lag(rate) over (partition by code order by date) last_rate
  from   ratetable
  )
  where  rate != last_rate
  /