>
>
>
> > or use a lateral subquery to surgically (fetch first 1) retrieve the
> first row when sorted by recency descending.
>
> I'm not sure that I see how to apply this when I need top-k, not top-1.
>
Fetch first k
It's just a modern limit clause.
David J.
Thanks both for your suggestions so far.
On Fri, Sep 13, 2024 at 8:43 AM David G. Johnston
wrote:
>
> On Friday, September 13, 2024, Willow Chargin 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
On Friday, September 13, 2024, Willow Chargin
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 descendi
On Thu, Sep 12, 2024 at 11:13 PM wrote:
>
> 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
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?
>
>
