Selecting rows where price changed

Question

I have a problem much like this one

SQL: selecting rows where column value changed from previous row

Although not in mysql in SQL Server. i tried ypercube's first answer jiri's answer and egor's as well. All of them just run for over 5 min with no results (one i let run over 10min). The table contains over a million records so i know this is a big part of the problem. I have a feeling ypercube's second answer might work well but don't know how to change this variable driven mysql query to SQL.

Any help would be appreciated.

SQL version 2008 r2

Basically i need to determine when a price has changed on table containing the price,productID, serialnumber and a datestamp.

I can get a quick list of what productids/serial numbers need to be checked to compare against. Sorry i did not include this earlier i was thinking i could just adapt a solution to fit it.

Shame. 2012 has `LAG`/`LEAD` that are more efficient than 2008 options. What is your table structure? — Martin Smith, Dec 23 '13 at 18:03
I can make a quick list of products that need to be queried and put it in a temp table but then i still have to run through the whole table to check for changes on those products so it doesn't help too much. — RustyH, Dec 23 '13 at 18:08
You're going to have to post some of your database structure if we are going to be able to help you. — Zane, Dec 23 '13 at 18:11
I posted the database design. Figured it out a much easier way with some creative grouping anyway, much faster than joining the table to itself — RustyH, Dec 23 '13 at 20:14

score 0 · Answer 1 · answered Dec 23 '13 at 18:12

A common table expression should do it fairly efficiently;

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY system ORDER BY timestamp) rn
  FROM TableX
)
SELECT a.timestamp, a.system, a.statusa, a.statusb
FROM cte a JOIN cte b ON a.system = b.system AND a.rn = b.rn+1
WHERE a.statusa <> b.statusa

An SQLfiddle to test with.

Selecting rows where price changed

1 Answers1