Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Finding "most recent" using daterange
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <98fd5198e1fe4e162f1166fd0575844de8bb7714.camel@loonybin.net>
Date: Wed, 22 May 2024 20:07:25 +0200
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <D29C1485-24A5-4602-8690-6FC6DD0E48F0@gmail.com>
References: <98fd5198e1fe4e162f1166fd0575844de8bb7714.camel@loonybin.net>
To: Rob Foehl <rwf@loonybin.net>
Archived-At: <https://www.postgresql.org/message-id/D29C1485-24A5-4602-8690-6FC6DD0E48F0%40gmail.com>
Precedence: bulk


> On 22 May 2024, at 09:58, Rob Foehl <rwf@loonybin.net> wrote:
>=20
> Coming back to PostgreSQL after a (decades-)long absence...  If I have
> something like:
>=20
> CREATE TABLE example (
> id integer NOT NULL,
> value text NOT NULL,
> dates daterange NOT NULL
> );
>=20
> INSERT INTO example VALUES
> (1, 'a', '[2010-01-01,2020-01-01)'),
> (1, 'b', '[2010-01-01,)'),
> (1, 'c', '[,2021-01-01)'),
> (2, 'd', '[2010-01-01,2021-01-01)'),
> (2, 'e', '[2015-01-01,2020-01-01)'),
> (3, 'f', '[2014-01-01,2016-01-01)'),
> (3, 'g', '[2013-01-01,)'),
> (3, 'h', '[2012-01-01,)'),
> (3, 'i', '[2013-01-01,2017-01-01)'),
> (4, 'j', '[2010-01-01,2015-01-01)');
>=20
> and I want to find the "most recent" value out of each group, meaning
> that having the greatest upper bound followed by the greatest lower
> bound, what I've managed to come up with thus far is:

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 =3D e.id
        AND (coalesce(upper(i.dates), 'infinity') > =
coalesce(upper(e.dates), 'infinity')
                OR (coalesce(upper(i.dates), 'infinity') =3D =
coalesce(upper(e.dates), 'infinity')
                AND coalesce(lower(i.dates), '-infinity') > =
coalesce(lower(e.dates), '-infinity'))
        )
);

 id | value |          dates         =20
----+-------+-------------------------
  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)

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.