Re: Finding "most recent" using daterange

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

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