SQL Server: how to select rows when changes happen?

Posted on

Question :

I have a question how to select row when changes happen.
Here is the table:

CreationDate | Value 
2019-06-04   | apple 
2019-06-01   | apple
2019-05-30   | apple 
2019-04-01   | orange 
2019-03-01   | apple
2019-02-01   | apple 
2019-01-01   | orange

If value doesn’t change, then remove from the table.
I sort by date from new to old.
I want select value changes rows.

I expect a table like this:

CreationDate | Value
2019-05-30   | apple
2019-04-01   | orange
2019-02-01   | apple
2019-01-01   | orange

I want a table of which changes happened.

Answer :

This should get you what you want. You want to look into using the LEAD function.

DECLARE @T TABLE
    (
    CreationDate DATE NOT NULL
    , [Value] VARCHAR(10) NOT NULL
    )

INSERT INTO @T
    (CreationDate, [Value])
VALUES ('6/4/2019', 'apple')
    , ('6/1/2019', 'apple')
    , ('5/30/2019', 'apple')
    , ('4/30/2019', 'orange')
    , ('4/1/2019', 'orange')
    , ('3/1/2019', 'apple')
    , ('2/1/2019', 'orange')

;WITH CTE_Source AS
    (
    SELECT TOP 100 PERCENT CreationDate, [Value], LEAD([Value]) OVER (ORDER BY (SELECT NULL)) AS PriorValue
    FROM @T AS T
    ORDER BY T.CreationDate, T.[Value]
    )
SELECT CreationDate, [Value] 
FROM CTE_Source
WHERE PriorValue IS NULL OR PriorValue <> [Value]

Leave a Reply

Your email address will not be published. Required fields are marked *