MIME-Version: 1.0
In-Reply-To: <CAALRJs5QkE-yWVdxQxOLXbM8DyPVV3M_=zfsPK17SV-zHQsASQ@mail.gmail.com>
References: <CAALRJs5ne=gPYG=FdeY-G0p9QyjXHhPhAyAsJ_evdCwG3vuhug@mail.gmail.com>
 <88e52d11-6185-473d-9eb2-673b62cb2426@gmx.net> <CAALRJs5QkE-yWVdxQxOLXbM8DyPVV3M_=zfsPK17SV-zHQsASQ@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 13 Sep 2024 08:43:54 -0700
Message-ID: <CAKFQuwYEtU7doien_XGh58vjRdsJezXp2BQvKdLcyVZQwhLmgg@mail.gmail.com>
Subject: Re: Functionally dependent columns in SELECT DISTINCT
To: Willow Chargin <postgresql@wchargin.com>
Cc: "shammat@gmx.net" <shammat@gmx.net>, 
	"pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000004dba54062202171e"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwYEtU7doien_XGh58vjRdsJezXp2BQvKdLcyVZQwhLmgg%40mail.gmail.com>
Precedence: bulk

--0000000000004dba54062202171e
Content-Type: text/plain; charset="UTF-8"

On Friday, September 13, 2024, Willow Chargin <postgresql@wchargin.com>
wrote:

> In reality I really do want the ID columns of the
> *most recent* items.
>

Use a window function to rank them and pull out rank=1, or use a lateral
subquery to surgically (fetch first 1) retrieve the first row when sorted
by recency descending.

David J.

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

On Friday, September 13, 2024, Willow Chargin &lt;<a href=3D"mailto:postgre=
sql@wchargin.com">postgresql@wchargin.com</a>&gt; wrote:<br><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;pa=
dding-left:1ex">In reality I really do want the ID columns of the<br>
*most recent* items.<br>
</blockquote><div><br></div><div>Use a window function to rank them and pul=
l out rank=3D1, or use a lateral subquery to surgically (fetch first 1) ret=
rieve the first row when sorted by recency descending.</div><div><br></div>=
<div>David J.=C2=A0</div>

--0000000000004dba54062202171e--