Re: Finding "most recent" using daterange - Mailing list pgsql-general

From Alban Hertroys
Subject Re: Finding "most recent" using daterange
Date
Msg-id D29C1485-24A5-4602-8690-6FC6DD0E48F0@gmail.com
Whole thread Raw
In response to Finding "most recent" using daterange  (Rob Foehl <rwf@loonybin.net>)
Responses Re: Finding "most recent" using daterange
List pgsql-general
> On 22 May 2024, at 09:58, Rob Foehl <rwf@loonybin.net> wrote:
>
> Coming back to PostgreSQL after a (decades-)long absence...  If I have
> something like:
>
> CREATE TABLE example (
> id integer NOT NULL,
> value text NOT NULL,
> dates daterange NOT NULL
> );
>
> 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)');
>
> 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 = 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'))
        )
);

 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)

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




pgsql-general by date:

Previous
From: Isaac Morland
Date:
Subject: Re: search_path and SET ROLE
Next
From: Daniel Gustafsson
Date:
Subject: Re: Missed compiler optimization issue in function select_rtable_names_for_explain