How to get rows with last update on column

Question

Goal: For each "IDCONT", i need to get the "DAY_ID" where i have the last change/update on "STATE_ID".

Example:

with reftable as (
 select 1 as PROCESSID, 'A' as IDCONT, 'X' as STATEID, '10' AS DAY_ID union all
 select 2 as PROCESSID, 'A' as IDCONT, 'X' as STATEID, '11' AS DAY_ID union all
 select 3 as PROCESSID, 'A' as IDCONT, 'Y' as STATEID, '12' AS DAY_ID union all
 select 4 as PROCESSID, 'A' as IDCONT, 'Y' as STATEID, '13' AS DAY_ID union all

 select 1 as PROCESSID, 'B' as IDCONT, 'N' as STATEID, '14' AS DAY_ID union all
 select 2 as PROCESSID, 'B' as IDCONT, 'N' as STATEID, '15' AS DAY_ID union all
 select 3 as PROCESSID, 'B' as IDCONT, 'M' as STATEID, '16' AS DAY_ID union all

 select 1 as PROCESSID, 'C' as IDCONT, 'X' as STATEID, '11' AS DAY_ID union all
 select 2 as PROCESSID, 'C' as IDCONT, 'X' as STATEID, '18' AS DAY_ID union all
) ...

Expected result:

PROCESSID   IDCONT   STATID   DAYID
3           A        Y        12
2           B        N        15
1           C        X        11        

I solved the problem with this:

...
SELECT IDCONT, STATEID, MIN(DAY_ID)
FROM REFTABLE 
WHERE (IDCONT, STATEID) IN (
   SELECT IDCONT, FIRST_VALUE(STATEID) OVER PARTITION BY IDCONT ORDER BY PROCESSID DESC) AS STATEID
   FROM REFTABLE
)

But i want to do the same without the need to call the table a 2nd time.

Thx!

Answer 1

Here is one method:

select r.*
from (select r.*,
             lag(stateid) over (partition by idcont order by day_id) as prev_stateid,
             first_value(stateid) over (partition by idcont order by day_id desc) as last_stateid
      from reftable r
     ) r
where stateid = last_stateid and (prev_stateid is null or prev_stateid <> stateid);

However, this does not handle the case where the state changes back to a previous state. That logic can be added in, if necessary.