From: Alban Hertroys <haramrae@gmail.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ben Clements <benhasgonewalking@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause
Date: Tue, 14 Mar 2023 09:01:28 +0100
Message-ID: <69BD603E-0CAF-40AD-AA83-D2484F126313@gmail.com> (raw)
In-Reply-To: <CAKFQuwZQ3MLbE+FP6mJb-YfH86hGzTj=dF_F_z_8RmjTwp6wBQ@mail.gmail.com>
References: <CAFKPu3vbSOwmYm=uUSgPuUvgVAFQvcwx1U5HD9Vy+GhHzQsO_g@mail.gmail.com>
<3766373.1678146043@sss.pgh.pa.us>
<CAApHDvrq4hy9PVgPuEpG66OAiTdjt+mdNSXtD-uSSWESdGt1TQ@mail.gmail.com>
<CAKFQuwZQ3MLbE+FP6mJb-YfH86hGzTj=dF_F_z_8RmjTwp6wBQ@mail.gmail.com>
> On 7 Mar 2023, at 4:11, David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> On Mon, Mar 6, 2023 at 7:51 PM David Rowley <dgrowleyml@gmail.com> wrote:
> 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.
>
> BOTTOM() remembers the highest value?
>
> Where this wouldn't work would be if multiple columns were
> required to tiebreak the sort.
>
> TOP(city, ROW(population, land_area)) ?
What should be the expected behaviour on a tie though?
Say that we count the number of districts or airfields or train stations per city and query for the one(s) with the most or least of them? There could well be multiple cities with the same max number, and there will be many cities with the same minimum number (namely 0).
Should the result be just the first of the maximums (or minimums) through some selection criterium (such as their alphabetical order), should that give each of the tied results, or should there be a means to define that behaviour?
I suppose a combination with FIRST and LAST could solve that issue?
Regards,
Alban Hertroys
--
There is always an exception to always.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: haramrae@gmail.com, david.g.johnston@gmail.com, dgrowleyml@gmail.com, tgl@sss.pgh.pa.us, benhasgonewalking@gmail.com
Subject: Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause
In-Reply-To: <69BD603E-0CAF-40AD-AA83-D2484F126313@gmail.com>
References: <CAFKPu3vbSOwmYm=uUSgPuUvgVAFQvcwx1U5HD9Vy+GhHzQsO_g@mail.gmail.com> <3766373.1678146043@sss.pgh.pa.us> <CAApHDvrq4hy9PVgPuEpG66OAiTdjt+mdNSXtD-uSSWESdGt1TQ@mail.gmail.com> <CAKFQuwZQ3MLbE+FP6mJb-YfH86hGzTj=dF_F_z_8RmjTwp6wBQ@mail.gmail.com> <69BD603E-0CAF-40AD-AA83-D2484F126313@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox