MIME-Version: 1.0
References: <98fd5198e1fe4e162f1166fd0575844de8bb7714.camel@loonybin.net>
In-Reply-To: <98fd5198e1fe4e162f1166fd0575844de8bb7714.camel@loonybin.net>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 22 May 2024 10:14:25 -0400
Message-ID: <CAKAnmmLBxrQ0n4+BPb_xBQKrDRoiq4eSKdQYDbnmo7KTQVFuRg@mail.gmail.com>
Subject: Re: Finding "most recent" using daterange
To: Rob Foehl <rwf@loonybin.net>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000068453406190b8f11"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmLBxrQ0n4%2BBPb_xBQKrDRoiq4eSKdQYDbnmo7KTQVFuRg%40mail.gmail.com>
Precedence: bulk

--00000000000068453406190b8f11
Content-Type: text/plain; charset="UTF-8"

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) desc, lower(dates) desc) from example)
  select id,value,dates from x where row_number = 1;

 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)


Cheers,
Greg

--00000000000068453406190b8f11
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">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 &#39;=
infinity&#39; involved, although you could write &#39;DESC NULLS FIRST&#39;=
 to be explicit about it.<div><br></div><div>with x as (select *, =C2=A0row=
_number() over (partition by id order by upper(dates) desc, lower(dates) de=
sc) from example)</div><div>=C2=A0 select id,value,dates from x where row_n=
umber =3D 1;<br></div><div><br></div><div>=C2=A0id | value | =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0dates<br>----+-------+-------------------------<br>=C2=
=A0 1 | b =C2=A0 =C2=A0 | [2010-01-01,)<br>=C2=A0 2 | d =C2=A0 =C2=A0 | [20=
10-01-01,2021-01-01)<br>=C2=A0 3 | g =C2=A0 =C2=A0 | [2013-01-01,)<br>=C2=
=A0 4 | j =C2=A0 =C2=A0 | [2010-01-01,2015-01-01)<br>(4 rows)<br></div><div=
><br></div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></div><=
/div>

--00000000000068453406190b8f11--