This idea is related to a video by Connor McDonald: The KEEP clause will KEEP your SQL queries SIMPLE
Connor talks about using any_value(...) keep (dense_rank first/last order by ...) to simplify aggregate queries:
We want to aggregate on a particular column, but get information from a different column, using a simple calculated column in the SELECT list. (paraphrased)
Like this: https://dbfiddle.uk/MQrHEIHd
--For each country, what city has the highest population? (where the country has more than one city)
--Include the city name as a column.
with cities (city, country, population) as (
select 'a1', 'a', 1 from dual union all
select 'a2', 'a', 3 from dual union all
select 'a3', 'a', 3 from dual union all
select 'b1', 'b', 1 from dual union all
select 'b3', 'b', 3 from dual union all
select 'b2', 'b', 3 from dual union all
select 'c1', 'c', 1 from dual
)
select
country,
count(*) as ct_cities,
max(population) as highest_population,
any_value(city) keep (dense_rank last order by population, city) as biggest_city -- !
from
cities
group by
country
having
count(*) > 1
COUNTRY CT_CITIES HIGHEST_POPULATION BIGGEST_CITY
------- --------- ------------------ ------------
a 3 3 a3
b 3 3 b3
As a novice, that technique seems useful; it allows us to keep queries short. But as mentioned in the video, the syntax is “admittedly, a bit cryptic to read”. So I'm wondering if simpler syntax would be possible.
For example, could Oracle implement the equivalent of PostgreSQL's optional FIRST() and LAST() aggregate functions? Erwin Brandstetter talks about it here: PostgreSQL equivalent to Oracle's MAX(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
https://dbfiddle.uk/oHHBAD_b
SELECT country
, count(*) AS ct_cities
, max(population) AS highest_population
, last(city ORDER BY population, city) AS biggest_city -- !
FROM cities
GROUP BY country
HAVING count(*) > 1;
To me, last(city ORDER BY population, city) is easier to read than any_value(city) keep (dense_rank last order by population, city).
Could functionality similar to first() and last() be implemented in Oracle?
Related article: db-oriented.com - ANY_VALUE and FIRST/LAST (KEEP)