CODEWITHSUNDEEP
sqlsql-server
Jordi
Jordi

Reputation: 2537

Compare data in consecutive rows

I have a table with 2 fields: TimeStamp (date & time) and Value (numeric).

I'd need an (efficient) query to find those rows where the Value field is smaller from the previous row, being them sorted by TimeStamp. I provide a small set of data as an example:

Timestamp                Value
-------------            -----------
2014/12/01 18:30:10      500
2014/12/01 18:30:20      510
2014/12/01 18:30:30      520
2014/12/01 18:30:40      530
2014/12/01 18:30:50      5        <- I want to have this row returned
2014/12/01 18:31:00      25
2014/12/01 18:31:10      40
2014/12/01 18:31:20      13       <- And this one as well.
2014/12/01 18:31:30      18
2014/12/01 18:31:40      23

A row will be inserted every 10 seconds and data will last for years, so I expect the number of rows to grow quite quickly.

If no efficient query can be produced, I'm thinking of creating a trigger on row insertion that will retrieve the previous row, check the Value field and, if the row being inserted has a smaller Value, insert a record into another table. What do you think about it?

Thanks!

Upvotes: 0

Views: 1893

Answers (3)

Eduard Uta
Eduard Uta

Reputation: 2607

Another solution would be like so: 

select *
from
    (select dt, value, ROW_NUMBER() OVER(ORDER BY dt asc) as RowNo
    from test) t1
where t1.value < 
    (select value 
    from
        (select dt, value, ROW_NUMBER() OVER(ORDER BY dt asc) as RowNo
        from test) t2
    where t2.RowNo = t1.RowNo - 1
)

Tested here: http://sqlfiddle.com/#!3/336bc/11

Upvotes: 0

Jordi
Jordi

Reputation: 2537

I never thought of correlated subqueries. I think this would work for me:

SELECT 
    t.TimeStamp, 
    t.Valor
FROM 
    Tabla1 AS t
WHERE 
    t.Valor <
    (
        SELECT TOP 1
            t2.Valor
        FROM
            Tabla1 AS t2
        WHERE
            t2.TimeStamp < t.TimeStamp
        ORDER BY
            t2.TimeStamp ASC
    )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

In SQL Server 2012+, you would use lag():

insert into othertable(col1 . . . )
    select t.*
    from (select t.*, lag(value) over (order by timestamp) as prev_value
          from table t
         ) t
    where value < prev_value;

For performance, you want an index on table(timestamp, value).

In earlier versions of SQL Server, you can use a correlated subquery or cross apply.

If you are doing this on a regular basis, such as every night, then you will want a where clause. Be careful about boundary conditions (if the value goes down just over midnight, you still want to catch that).

Upvotes: 2

Related Questions