CODEWITHSUNDEEP
sql
navid karampour
navid karampour

Reputation: 93

how can i select the any changes in on column?

I have a table like this:

state         date
new           01/01/2018
new           02/01/2018
active        05/01/2018
active        09/01/2018
active        10/01/2018
new           12/01/2018
active        13/01/2018
active        14/01/2018
close         15/01/2018

and I need to any change in state column whit the min date of each category.

final table:

state         date
new          01/01/2018
active       05/01/2018
new          12/01/2018
close        15/01/2018

How can I do it in SQL?

Upvotes: 0

Views: 62

Answers (2)

Joakim Danielson
Joakim Danielson

Reputation: 52098

Here is a solution using WITH and LAG

WITH state_change (state, st_date, next_state) AS
  (SELECT state , st_date, 
          LAG(state, 1, '') OVER(ORDER BY st_date) as s2
   FROM table
 )
SELECT state, st_date
FROM state_change
WHERE state != next_state
ORDER BY st_date ASC

Output (that includes the second change to active)

state   st_date
new     2018-01-01
active  2018-01-05
new     2018-01-12
active  2018-01-14
close   2018-01-15

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Since you haven't tagged any DBMS, so i would go with row_number() as it seem gaps-and-islands issue :

select state, min(date)
from (select t.*,
             row_number() over (order by date) as seq1,
             row_number() over (partition by state order by date) as seq2
      from table t
      ) t
group by state, (seq1-seq2);

Upvotes: 1

Related Questions