Simplify Oracle aggregation queries (using the KEEP clause)

[Bud]

I thought I'd share an SQL technique I came across recently — for anyone writing aggregation queries for Oracle:

YouTube video: The KEEP clause will KEEP your SQL queries SIMPLE

Paraphrased:
When doing aggregation, one of the most common requirements is: once we've aggregated on a particular column, we actually want to know information from a different column. 

Example:
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.

I recommend watching that video.

Then, for an explanation of the logic of the various SQL keywords, I'd suggest reading the answer in this post:
Stack Overflow - Explanation of KEEP in Oracle FIRST/LAST

One of the biggest challenges I have when writing SQL queries is keeping them short enough so that they're readable. I think the above technique will help.

While this technique doesn't pertain specifically to spatial data, I often write non-spatial queries like the above query on spatial data. I imagine others do too.

Related:

Oracle Groundbreakers Community - Idea: FIRST() and LAST() aggregate functions

[TanuHoque]

Thanks for sharing this @Bud. This so cool. 🥇

Do you happen to know if this only specific to Oracle? Do other databases support this or something similar?

[Bud]

SQL Server:

Stack Overflow: SQL Server equivalent to Oracle's MAX(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)

Answer:

SQL Server does not implement this. 

Edit: @TanuHoque, I submitted an idea asking Microsoft to add that functionality to T-SQL:
Idea - SQL Server equivalent to Oracle's MAX(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)

Feel free to vote for that idea.

Related: SQL Server Ideas

[Bud]

PostgreSQL: 

DBA Stack Exchange: PostgreSQL equivalent to Oracle's MAX(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)

Edit: @TanuHoque, I submitted a post to the "General" PostgreSQL mailing list -- requesting that the functionality be added to PostgreSQL. The post/email was called "Idea: PostgreSQL equivalent to Oracle's KEEP clause". The content of the request was the same as the SQL Server one I mentioned previously, except it was for PostgreSQL.

Related:

• DBA Stack Exchange - How to request an enhancement to PostgreSQL
• PostgreSQL Mailing Lists

[Bud]

SQLite

@TanuHoque You'll find this SQLite answer interesting: SQLite equivalent to Oracle's MAX(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)

See: 2.5. Bare columns in an aggregate query

[TanuHoque]

Thanks a lot @Bud 

Appreciate all your help very much. I'm glad that SQLite has some support for this. I hope they will grow that support and other databases will follow the suite.

[Bud]

For what it's worth, I've been using Oracle's new ANY_VALUE() aggregate function instead of the MAX() aggregate function: 

ANY_VALUE(CITY) KEEP (DENSE_RANK FIRST ORDER BY POPULATION DESC)

Reason (from a colleague):

Knowing that you have max(city) there but that it will actually ignore that and get the city for the one with the highest population because of the KEEP seems counterintuitive.

While ANY_VALUE() will be "ignored" as well, at least it is more self-documenting. It suggests that the real logic  is in the other keywords:

... KEEP (DENSE_RANK FIRST ORDER BY POPULATION DESC)

Related comments about ANY_VALUE(): https://stackoverflow.com/questions/65866812/explanation-of-keep-in-oracle-first-last/65868727?nored...

Comments in the original YouTube video:

db-oriented.com: ANY_VALUE and FIRST/LAST (KEEP)