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

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'))
        )
);