Idea: PostgreSQL equivalent to Oracle's KEEP clause

From: Ben Clements <benhasgonewalking(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Idea: PostgreSQL equivalent to Oracle's KEEP clause
Date: 2023-03-06 22:06:27
Message-ID: CAFKPu3vbSOwmYm=uUSgPuUvgVAFQvcwx1U5HD9Vy+GhHzQsO_g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have an idea/request for enhancement to PostgreSQL (I'm new to PostgreSQL
and this mailing list).
------------------------------------------------
Idea:

There's a technique in Oracle SQL that can be used to simplify aggregation
queries:
*Aggregate on a particular column, but get information from a different
column, using a simple calculated column in the SELECT list.*

--Oracle
--For a given country, what city has the highest population? (where the
country has more than one city)
--Include the city name as a column.
select
country,
count(*),
max(population),
max(city) keep (dense_rank first order by population desc)
from
cities
group by
country
having
count(*) > 1

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)

There are a number of ways to achieve that kind of thing using PostgreSQL.
I want a solution that lets me do it in a calculated column -- all within a
single SELECT query (no subqueries, joins, WITH, etc.).

Could that functionality be added to PostgreSQL?

Related:

- YouTube - The KEEP clause will KEEP your SQL queries SIMPLE (Oracle)
<https://www.youtube.com/watch?v=AlTI_ZUyE0U&t=20s>
- Stack Overflow - Explanation of KEEP in Oracle FIRST/LAST
<https://stackoverflow.com/questions/65866812/explanation-of-keep-in-oracle-first-last/65868727#65868727>
- DBA Stack Exchange - PostgreSQL equivalent to Oracle's MAX(...) KEEP
(DENSE_RANK FIRST/LAST ORDER BY ...)
<https://dba.stackexchange.com/a/324429/100880>

Thanks,

-Ben

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-03-06 23:40:43 Re: Idea: PostgreSQL equivalent to Oracle's KEEP clause
Previous Message Adrian Klaver 2023-03-06 21:56:06 Re: garbage data back