public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: [email protected]
Subject: Re: Functionally dependent columns in SELECT DISTINCT
Date: Fri, 13 Sep 2024 08:13:25 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAALRJs5ne=gPYG=FdeY-G0p9QyjXHhPhAyAsJ_evdCwG3vuhug@mail.gmail.com>
References: <CAALRJs5ne=gPYG=FdeY-G0p9QyjXHhPhAyAsJ_evdCwG3vuhug@mail.gmail.com>

Willow Chargin schrieb am 13.09.2024 um 07:20:
> Hello! Postgres lets us omit columns from a GROUP BY clause if they are
> functionally dependent on a grouped key, which is a nice quality-of-life
> feature. I'm wondering if a similar relaxation could be permitted for
> the SELECT DISTINCT list?
>
> I have a query where I want to find the most recent few items from a
> table that match some complex condition, where the condition involves
> joining other tables. Here's an example, with two approaches:


What about using DISTINCT ON () ?
    SELECT DISTINCT ON (items.id) items.*
    FROM items
      JOIN parts ON items.id = parts.item_id
    WHERE part_id % 3 = 0
    ORDER BY items.id,items.create_time DESC
    LIMIT 5;

This gives me this plan: https://explain.depesz.com/s/QHr6 on 16.2  (Windows, i7-1260P)

view thread (3+ messages)  latest in thread

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]
  Subject: Re: Functionally dependent columns in SELECT DISTINCT
  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