public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alban Hertroys <[email protected]>
To: Rob Foehl <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Finding "most recent" using daterange
Date: Wed, 22 May 2024 20:07:25 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>


> On 22 May 2024, at 09:58, Rob Foehl <[email protected]> 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.

view thread (2+ messages)

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]
  Subject: Re: Finding "most recent" using daterange
  In-Reply-To: <[email protected]>

* 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