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
