Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Idea: FIRST() and LAST() aggregate functions

User_1871Mar 14 2023 — edited Mar 19 2023

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)

Comments
Post Details
Added on Mar 14 2023
10 comments
617 views