| 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: | Whole Thread | Raw Message | 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
| 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 |