From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Rob Foehl <rwf(at)loonybin(dot)net> |
Cc: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Finding "most recent" using daterange |
Date: | 2024-05-22 18:07:25 |
Message-ID: | D29C1485-24A5-4602-8690-6FC6DD0E48F0@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> On 22 May 2024, at 09:58, Rob Foehl <rwf(at)loonybin(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2024-05-22 18:12:36 | Re: Missed compiler optimization issue in function select_rtable_names_for_explain |
Previous Message | Isaac Morland | 2024-05-22 18:01:54 | Re: search_path and SET ROLE |