On Wed, May 22, 2024 at 11:07 AM Alban Hertroys <haram...@gmail.com> 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 *http://agency-software.org/ <http://agency-software.org/>* *https://demo.agency-software.org/client <https://demo.agency-software.org/client>* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.
