Oracle AI 26ai

While reading the very interesting Oracle AI Database 26ai New Features Guide, I got to know that there is a new “QUALIFY” filter clause (there is also a new “FILTER” clause). However, what I found more interesting in the documentation was this sentence:

The order of operations is as follows: FROM → WHERE → GROUP BY → HAVING → WINDOW → QUALIFY → DISTINCT → ORDER BY → FETCH FIRST.

This is somehow a back to the roots and basic SQL information that I had in the back of my head, but never though really end-to-end about.

Here an example of query using all except DISTINCT elements of the operations order:

SELECT region, city, AVG(temperature), AVG(AVG(temperature)) OVER w AS avg_temp_region 
    FROM city, regions 
    WHERE city.region_id = region.region_id 
    GROUP BY region, city 
    HAVING region NOT LIKE 'LISBON' 
    WINDOW w AS (PARTITION BY region)
    QUALIFY avg_temp_region > 10
    ORDER BY region
    FETCH FIRST 5 ROWS ONLY;

I don’t think I could write a SQL like that, but at least now I know how it looks like.