public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ken Tanzer <[email protected]>
To: Alban Hertroys <[email protected]>
Cc: Rob Foehl <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Finding "most recent" using daterange
Date: Wed, 22 May 2024 14:21:22 -0700
Message-ID: <CAD3a31W29bt9XOJbWT8=ZC7dqBFXd=bYgNLDUcm4+o=mzrFFtQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On Wed, May 22, 2024 at 11:07 AM Alban Hertroys <[email protected]> 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>*
[email protected]
(253) 245-3801
Subscribe to the mailing list
<[email protected]?body=subscribe> to
learn more about AGENCY or
follow the discussion.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Finding "most recent" using daterange
In-Reply-To: <CAD3a31W29bt9XOJbWT8=ZC7dqBFXd=bYgNLDUcm4+o=mzrFFtQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox