On Wed, May 22, 2024 at 11:07 AM Alban Hertroys <haramrae@xxxxxxxxx> wrote:
Sounds like a good candidate for using EXISTS to prove that no more recent value exists for a given id:
SELECT e.id, e.value, e.dates
FROM example AS e
WHERE NOT EXISTS (
SELECT 1
FROM example AS i
WHERE i.id = e.id
AND (coalesce(upper(i.dates), 'infinity') > coalesce(upper(e.dates), 'infinity')
OR (coalesce(upper(i.dates), 'infinity') = coalesce(upper(e.dates), 'infinity')
AND coalesce(lower(i.dates), '-infinity') > coalesce(lower(e.dates), '-infinity'))
)
);
Not sure if I'm missing something, but what about just using DISTINCT?
SELECT DISTINCT ON (id) id,value,dates FROM example ORDER BY id,upper(dates) desc, lower(dates) desc;
id | value | dates
----+-------+-------------------------
1 | b | [2010-01-01,)
2 | d | [2010-01-01,2021-01-01)
3 | g | [2013-01-01,)
4 | j | [2010-01-01,2015-01-01)
(4 rows)
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
