Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause

On Tue, 7 Mar 2023 at 12:40, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Ben Clements <benhasgonewalking@gmail.com> writes:
> > As shown above, the following calculated column can bring in the city name,
> > even though the city name isn't in the GROUP BY:
> >    max(city) keep (dense_rank first order by population desc)
>
> You haven't really explained what this does, let alone why it can't
> be implemented with existing features such as FILTER and ORDER BY.

(It wasn't clear to me until I watched the youtube video.) 

Likely KEEP is more flexible than just the given example but I think
that something similar to the example given could be done by inventing
a TOP() and BOTTOM() aggregate. Then you could write something like:

select
   country,
   count(*),
   max(population),
   bottom(city, population)
from
   cities
group by
   country
having
   count(*) > 1

the transfn for bottom() would need to remember the city and the
population for the highest yet seen value of the 2nd arg.

Where this wouldn't work would be if multiple columns were
required to tiebreak the sort.

I'd assume since the whole thing can be done with
a subquery that the entire point of having special syntax for this
would be because we don't want to pay the price of looking at the
table twice, i.e. performance must matter, so the ability to have
parallel aggregates here seems good.