Aparna Aparna - 3 months ago 9
SQL Question

In SQL how to not select rows where a column value is the same as the one in the row above it

I have a table with column Value and Timestamp. The values keep coming in per second. So I would have data as follows

V1 T1
V1 T2
V2 T3
V3 T4..


etc.. So The timestamps would keep increasing, but the values can be the same that they don't change.
I want to select only the rows where the value is different than the previous row.
So I want to reduce the data and select only the records where there is a change in the data.
Would anybody know how to do this?

DVT DVT
Answer

This only apply to SQL Server 2012 or later

WITH cte AS (
    SELECT
        V,T,
        LAG(V) OVER (ORDER BY T) AS LAST_V
    FROM
        TABLE
    )
SELECT
    V, T
FROM
    cte
WHERE
    V <> LAST_V OR LAST_V IS NULL;

The LAST_V is the value of V in the previous row, ordered by T. So if V <> LAST_V it means V is not the same as LAST_V which is what was asked. The case LAST_V IS NULL means you are at the first row.

Comments