CODEWITHSUNDEEP
sqlsql-server
Mike Sickler
Mike Sickler

Reputation: 34451

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

Upvotes: 6

Views: 28712

Answers (7)

David Aldridge
David Aldridge

Reputation: 52346

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
) my_tb
where  my_tb.rate != my_tb.last_rate

Upvotes: 6

Raj
Raj

Reputation: 6830

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

Upvotes: 0

dustyburwell
dustyburwell

Reputation: 5813

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

Upvotes: 0

David M
David M

Reputation: 72870

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)

Upvotes: 15

boj
boj

Reputation: 11395

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.

Upvotes: 0

Konstantin Tarkus
Konstantin Tarkus

Reputation: 38378

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'

Upvotes: 0

JoshBerke
JoshBerke

Reputation: 67068

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.

Upvotes: 0

Related Questions