Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pbzbH-0000dJ-DG for pgsql-general@arkaria.postgresql.org; Tue, 14 Mar 2023 08:01:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pbzbG-0000Zc-4Q for pgsql-general@arkaria.postgresql.org; Tue, 14 Mar 2023 08:01:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pbzbF-0000ZE-Pd for pgsql-general@lists.postgresql.org; Tue, 14 Mar 2023 08:01:37 +0000 Received: from mail-ed1-x530.google.com ([2a00:1450:4864:20::530]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pbzbC-0006TA-Ma for pgsql-general@lists.postgresql.org; Tue, 14 Mar 2023 08:01:36 +0000 Received: by mail-ed1-x530.google.com with SMTP id x3so58320063edb.10 for ; Tue, 14 Mar 2023 01:01:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; t=1678780892; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=yIVTu/kVZj+1rD2bu1seNImwRl44Q/DDbgHU1yS86X4=; b=mDHOzKlKwA5SYUXyJBC4I151FqKmMpUz25UuXKyxraQBMTDkeDFH/APfl/I3Dikppm yxG9k8I+6hv4Df5W+drt40gl4iiALeDVhZsFWzLTshyMv1Wnu6/pq2zxhz/fWA8aYzPx EVQIagaxna1BlXf1k7i41iDaB7oaTNcHWqKlkLGQ15J736zaAbFtTvy7Tqe9VcJURZfT +IIja/0X8Qh6uC8wL1gtB1Zs93qHgkvE0sqN8Dfg0n80bbuL8nTZNbm/3Qg45PsrqWz/ 7gFWumpbqqE1rAVhZ/UliXrOKEOGcuArWm0MR1oPxbKRfwzj6lkRbDi7BmschUH4Nzow kT+w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1678780892; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=yIVTu/kVZj+1rD2bu1seNImwRl44Q/DDbgHU1yS86X4=; b=nm/AXxzpi6JtaUnjgqIB/fqm/6F3tkqLnmAEb75iv/pHFqpIlIElCcnZZpT/m7d3z3 M8rqWe+mMgWACoBrfEpGVQq/X8SlJxYUODL36PVdQ/Nn63x8eviCkzijFR22PUjab0Gm yybWhLgDUWecBIHO5WkZzqwS38HsOJgP5sppovPEKwuix15SssDACmvNa/Pjm1/sWzGJ xKdY1PbdWJMEJrkfAEi/ozVnKC17wd9mce9QfJXlE9mnGwOqtCJzgguV3NmBukr4OTDA T/23Sj5aSzv5xShIv+TGjjzyREbuXBOo/sH3uAwWXwxCprAvuEBDxIzJX74vCf4zMM85 sjaw== X-Gm-Message-State: AO0yUKX2W1A1wX0oS7Xx3dylOHRQHtJaLfpXa0kbGSs+Czyh8WehR1eT 9Jypa0838PPHl1cObjqTWbo= X-Google-Smtp-Source: AK7set/0GDbitWgudZSg4A24ABMoPu8+wH+gcnHvWLUyciK9ZKkeYt4WqO/Tqs18ttcvL6OY5AYduA== X-Received: by 2002:a17:906:5f90:b0:92b:f019:a9ef with SMTP id a16-20020a1709065f9000b0092bf019a9efmr1424171eju.31.1678780891901; Tue, 14 Mar 2023 01:01:31 -0700 (PDT) Received: from hollewijn.internal (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id q19-20020a17090676d300b00922f76decefsm772532ejn.99.2023.03.14.01.01.30 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 14 Mar 2023 01:01:31 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause From: Alban Hertroys In-Reply-To: Date: Tue, 14 Mar 2023 09:01:28 +0100 Cc: David Rowley , Tom Lane , Ben Clements , pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <69BD603E-0CAF-40AD-AA83-D2484F126313@gmail.com> References: <3766373.1678146043@sss.pgh.pa.us> To: "David G. Johnston" X-Mailer: Apple Mail (2.3608.120.23.2.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 7 Mar 2023, at 4:11, David G. Johnston = wrote: >=20 > On Mon, Mar 6, 2023 at 7:51=E2=80=AFPM David Rowley = wrote: > On Tue, 7 Mar 2023 at 12:40, Tom Lane wrote: > > > > Ben Clements 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. >=20 > (It wasn't clear to me until I watched the youtube video.)=20 >=20 > 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: >=20 > select > country, > count(*), > max(population), > bottom(city, population) > from > cities > group by > country > having > count(*) > 1 >=20 > the transfn for bottom() would need to remember the city and the > population for the highest yet seen value of the 2nd arg. >=20 > BOTTOM() remembers the highest value? > =20 > Where this wouldn't work would be if multiple columns were > required to tiebreak the sort. >=20 > 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.