Question:
Imagine the data:Answer:
I would attack this in two parts. The first would make sure only the latest from a single date is kept. The second numbers the rows starting with the latest.with by_day as (
select *,
updated_at::date !=
lag(updated_at::date) over (partition by id
order by updated_at desc) keep
from imagined_data
), numbered as (
select *, row_number() over (partition by id
order by updated_at desc) as rn
from by_day
where coalesce(keep, true)
)
select id, audit_id, val, updated_at
from numbered
where rn <= 2;
[/code]
If you have better answer, please add a comment about this, thank you!