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
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 |