CODEWITHSUNDEEP
mysqlsqlgaps-and-islands
seequill
seequill

Reputation: 37

SQL How to retrieve first row when value changed?

I have a table with ID's, dates, and a flag. I'd like to retrieve the first record within a certain year where the flag has explicitly changed from 0 to 1. 

ID    DATE        FLAG
1    2019-01-31     0
1    2019-02-28     1
1    2019-03-31     1

2    2019-01-31     1
2    2019-02-28     1
2    2019-03-31     1
2    2019-04-30     0
2    2019-05-31     1

3    2019-01-31     0
3    2019-02-28     1
3    2019-03-31     0
3    2019-04-30     1

So my expected result would be for 1 it would be 2019-01-31, 2 would be excluded, and 3 would be 2019-02-28.

So far I have this query which works for the most part, but when I'm doing QA, it seems the query is not excluding those who start at 1, switch to 0, then back to 1.

Query

SELECT t.ID,
    Date, 
    Flag
 FROM table t
WHERE Flag = '1' 
  AND t.Date > (SELECT MAX(t2.Date) FROM table t2 WHERE t2.ID = t.ID AND t2.Flag = '0' AND t2.Date BETWEEN '2019-01-01' AND '2019-12-31') 
AND t.Date BETWEEN '2019-01-01' AND '2019-12-31'
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date);

Any help would be great, thanks!

EDIT: I've added SQLFiddle

Upvotes: 3

Views: 2515

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use lag() to get the previous flag and then aggregate:

select id, min(date)
from (select *,
             lag(flag) over (partition by id, year(date) order by date) as prev_flag
      from temp
     ) t
where flag = 1 and prev_flag = 0
group by id, year(date);

SQL Fiddle

This returns the results for each year. You can, of course, filter by the year in the subquery if you like.

Upvotes: 4

GMB
GMB

Reputation: 222492

I understand that you want the first 1 - for series that start with 0 only.

If you are running MySQL 8.0, you can use window functions for this:

select id, min_date
from (
    select 
        id,
        flag,
        row_number() over(partition by id order by date) rn,
        min(case when flag = 1 then date end) over(partition by id) min_date
    from mytable
) t
where rn = 1 and flag = 0

The subquery ranks records having the same id by date and computes the date of the first 1 flag in the group; thie outer query filters on the first record per group, ensures that it has a 0 flag, and displays the computed date.

Upvotes: 1

Related Questions