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]