I have a sql table similar to the below. I would like to get the last time it was changed from 0 to 1 and the last time it changed back to 0 (highlighted below for an example id).
What I have tried is:
select * from Table t1 join Table t2 on t1.id = t2.id join Table t3 on t1.id = t3.id
where t1.flag = 1 and t2.flag = 0 and t3.flag
group by t1.id
having min(t1.createdtime) between max(t2.createdtime) and min(t3.createdtime)
For this dataset, you could use lag() to bring in the flag of the previous row, use it as a filter condition, and then aggregate:
select
id,
max(createdtime) createdtime,
flag
from (
select
t.*,
lag(flag) over(partition by id order by createdtime) lagflag
from mytable t
) t
where (flag = 0 and lagflag = 1) or (flag = 1 and lagflag = 0)
group by id, flag
You can use lag() to get the recent flag. So you can filter for changes, i.e. when the (current) flag and the recent flag relate as desired. To get only the most recent of the changes you can filter by row_number().
SELECT z.id,
z.createdtime,
z.flag
FROM (SELECT y.id,
y.createdtime,
y.flag FROM (SELECT x.id,
x.createdtime,
x.flag,
row_number() OVER (PARTITION BY x.id
ORDER BY x.createdtime DESC) rn
FROM (SELECT t.id,
t.createdtime,
t.flag,
lag(t.flag) OVER (PARTITION BY t.id
ORDER BY createdtime) recentflag
FROM elbat t) x
WHERE x.flag = 0
AND x.recentflag = 1) y
WHERE y.rn = 1
UNION ALL
SELECT y.id,
y.createdtime,
y.flag FROM (SELECT x.id,
x.createdtime,
x.flag,
row_number() OVER (PARTITION BY x.id
ORDER BY x.createdtime DESC) rn
FROM (SELECT t.id,
t.createdtime,
t.flag,
lag(t.flag) OVER (PARTITION BY t.id
ORDER BY createdtime) recentflag
FROM elbat t) x
WHERE x.flag = 1
AND x.recentflag = 0) y
WHERE y.rn = 1) z
ORDER BY z.createdtime DESC;
Use lag() in a CTE to get the previous flag for each row and then NOT EXISTS:
with cte as(
select *, lag(flag) over(partition by id order by createdtime) prevflag
from tablename
)
select c.id, c.createdtime, c.flag
from cte c
where c.flag <> c.prevflag
and not exists (
select 1 from cte
where id = c.id and flag = c.flag and prevflag = c.prevflag and createdtime > c.createdtime
)
order by c.createdtime
Or:
with cte as(
select *, lag(flag) over(partition by id order by createdtime) prevflag
from tablename
)
select id, max(createdtime) createdtime, flag
from cte
where flag <> prevflag
group by id, flag
order by createdtime
See the demo.
Results:
> id | createdtime | flag
> -: | :------------------ | ---:
> 5 | 2019-11-02 14:30:00 | 1
> 5 | 2020-08-01 14:30:00 | 0
