Re: Finding "most recent" using daterange

On Wed, May 22, 2024 at 11:07 AM Alban Hertroys <haramrae@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.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.