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)
- Stack Overflow - Explanation of KEEP in Oracle FIRST/LAST
- DBA Stack Exchange - PostgreSQL equivalent to Oracle's MAX(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
Thanks,
-Ben
