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), any_value(city) keep (dense_rank first order by population desc) --<<-- from cities group by country having count(*) > 1
As shown above, the following column can bring in the city name, even though the city name isn't in the GROUP BY:
any_value(city) keep (dense_rank first order by population desc)
There are a number of ways to achieve that kind of thing using SQL. I'm looking for a solution in PostgreSQL that lets me do it in a calculated column -- all within a single SELECT query (no subqueries, joins, WITH, etc.).
Question: Is there equivalent functionality to Oracle's
ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...) in PostgreSQL?
- YouTube: The KEEP clause will KEEP your SQL queries SIMPLE (Oracle)
- Stack Overflow: Explanation of KEEP in Oracle FIRST/LAST
- db-oriented.com: ANY_VALUE and FIRST/LAST (KEEP)
- DBA Stack Exchange: How to request an enhancement to PostgreSQL
ANY_VALUE(), since I think
ANY_VALUE() is easier to read.
Ties can be broken by adding
, city desc to the
order by, making it deterministic:
any_value(city) keep (dense_rank first order by population desc, city desc)