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

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.

Answer 2

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'

Answer 3

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.

Answer 4

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)

Answer 5

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

Answer 6

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

Answer 7

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
/