topic Re: Simplify Oracle aggregation queries (using KEEP, DENSE_RANK, & FIRST/LAST) in ArcGIS Enterprise Questions

Simplify Oracle aggregation queries (using the KEEP clause)

Bud — Tue, 14 Mar 2023 15:34:57 GMT

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.

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

Re: Simplify Oracle aggregation queries (using KEEP and FIRST/LAST)

TanuHoque — Fri, 03 Mar 2023 17:32:51 GMT

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?

Re: Simplify Oracle aggregation queries (using KEEP and FIRST/LAST)

Bud — Mon, 06 Mar 2023 22:20:37 GMT

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

Re: Simplify Oracle aggregation queries (using KEEP and FIRST/LAST)

Bud — Mon, 06 Mar 2023 22:21:25 GMT

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.

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

Re: Simplify Oracle aggregation queries (using KEEP and FIRST/LAST)

Bud — Thu, 09 Mar 2023 16:20:34 GMT

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

Re: Simplify Oracle aggregation queries (using KEEP, DENSE_RANK, & FIRST/LAST)

TanuHoque — Sat, 11 Mar 2023 01:34:05 GMT

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.

Re: Simplify Oracle aggregation queries (using the KEEP clause)

Bud — Sun, 19 Mar 2023 23:44:09 GMT

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)

Comments in the original YouTube video:

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