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