On Wed, May 22, 2024 at 11:07 AM Alban Hertroys wrote:
>
> 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
>
> On 22 May 2024, at 09:58, Rob Foehl 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-0
On Wed, 22 May 2024 at 11:36, Greg Sabino Mullane
wrote:
> Oh, you are right - NULLS LAST, got my -infinity crossed with my infinity.
> :)
>
NULLS LAST for lower bound, NULLS FIRST for upper bound.
The other way around if you were doing an ascending sort.
Oh, you are right - NULLS LAST, got my -infinity crossed with my infinity.
:)
Cheers,
Greg
On Wed, 22 May 2024 at 10:15, Greg Sabino Mullane
wrote:
> This is a good candidate for a window function. Also note that nulls
> already get sorted correctly by the DESC so no need to get 'infinity'
> involved, although you could write 'DESC NULLS FIRST' to be explicit about
> it.
>
> with x as
This is a good candidate for a window function. Also note that nulls
already get sorted correctly by the DESC so no need to get 'infinity'
involved, although you could write 'DESC NULLS FIRST' to be explicit about
it.
with x as (select *, row_number() over (partition by id order by
upper(dates) d
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', '[20
