{"id":1116,"date":"2026-04-01T08:56:04","date_gmt":"2026-04-01T06:56:04","guid":{"rendered":"https:\/\/anjo.pt\/keyword-oracle\/?p=1116"},"modified":"2026-04-01T08:56:04","modified_gmt":"2026-04-01T06:56:04","slug":"sql-and-how-oracle-filter-the-resultset","status":"publish","type":"post","link":"https:\/\/anjo.pt\/keyword-oracle\/2026\/04\/01\/sql-and-how-oracle-filter-the-resultset\/","title":{"rendered":"SQL and how Oracle filter the resultset"},"content":{"rendered":"\n<p>While reading the very interesting <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/26\/nfcoa\/oracle-ai-database-26ai-new-features-guide.pdf\">Oracle AI Database 26ai New Features Guide<\/a>, I got to know that there is a new &#8220;QUALIFY&#8221; filter clause (there is also a new &#8220;FILTER&#8221; clause). However, what I found more interesting in the documentation was this sentence:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>The order of operations is as follows:\u00a0<code>FROM<\/code>\u00a0\u2192\u00a0<code>WHERE<\/code>\u00a0\u2192 <code>GROUP BY<\/code>\u00a0\u2192\u00a0<code>HAVING<\/code>\u00a0\u2192\u00a0<code>WINDOW<\/code>\u00a0\u2192\u00a0<code>QUALIFY<\/code>\u00a0\u2192\u00a0<code>DISTINCT<\/code>\u00a0\u2192\u00a0<code>ORDER BY<\/code>\u00a0\u2192\u00a0<code>FETCH FIRST<\/code>.<\/p>\n<\/blockquote>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>Here an example of query using all except DISTINCT elements of the operations order:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\nSELECT region, city, AVG(temperature), AVG(AVG(temperature)) OVER w AS avg_temp_region \n    FROM city, regions \n    WHERE city.region_id = region.region_id \n    GROUP BY region, city \n    HAVING region NOT LIKE &#039;LISBON&#039; \n    WINDOW w AS (PARTITION BY region)\n    QUALIFY avg_temp_region &gt; 10\n    ORDER BY region\n    FETCH FIRST 5 ROWS ONLY;\n<\/pre><\/div>\n\n\n<p>I don&#8217;t think I could write a SQL like that, but at least now I know how it looks like.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>While reading the very interesting Oracle AI Database 26ai New Features Guide, I got to know that there is a new &#8220;QUALIFY&#8221; filter clause (there is also a new &#8220;FILTER&#8221; clause). However, what I found more interesting in the documentation was this sentence: The order of operations is as follows:\u00a0FROM\u00a0\u2192\u00a0WHERE\u00a0\u2192 GROUP BY\u00a0\u2192\u00a0HAVING\u00a0\u2192\u00a0WINDOW\u00a0\u2192\u00a0QUALIFY\u00a0\u2192\u00a0DISTINCT\u00a0\u2192\u00a0ORDER BY\u00a0\u2192\u00a0FETCH FIRST. This [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,90],"tags":[],"class_list":{"0":"post-1116","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-oracle","7":"category-oracle-ai-26ai","8":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=1116"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1116\/revisions"}],"predecessor-version":[{"id":1117,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/1116\/revisions\/1117"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=1116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=1116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=1116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}