--- title: "QUALIFY and GROUP BY ALL: which databases support them in 2026" description: "Oracle 26ai runs QUALIFY and bare GROUP BY ALL but not ORDER BY ALL; SQL Server 2025, PostgreSQL 19 RC1, MySQL and SQLite run none. Exact errors." url: "https://jamsql.com/blog/2026-10-17-qualify-group-by-all-order-by-all-support/" html_url: "https://jamsql.com/blog/2026-10-17-qualify-group-by-all-order-by-all-support/" generated: "2026-10-25T01:35:22.663Z" --- Published: 2026-10-17 # `QUALIFY` and `GROUP BY ALL`: which databases support them in 2026 Among the engines we ran, only Oracle AI Database 26ai accepts `QUALIFY` and a bare `GROUP BY ALL`. SQL Server 2025, PostgreSQL 16 and 19 RC1, MySQL 8.0, MariaDB and SQLite reject all three of `QUALIFY`, `GROUP BY ALL` and `ORDER BY ALL`. MySQL 9.5 parses `QUALIFY` and then refuses to run it. Snowflake, DuckDB and Databricks document all three; Microsoft Fabric Warehouse documents them as well. Every engine we tested accepts the portable rewrite, a window function in a subquery with a `WHERE`. We ran one identical query per feature on nine server versions on 17 October 2026 and kept the exact error text. Warehouse engines we did not run are marked *not tested by us* and linked to the vendor page we read. ## Support matrix, tested 17 October 2026 Database (version tested) `QUALIFY` Bare `GROUP BY ALL` `ORDER BY ALL` Rewrite with subquery SQL Server 2025 (RTM-CU8, 17.0.4075.5) No: Msg 102 No: Msg 156 No: Msg 156 Works PostgreSQL 19 RC1 (19~rc1) No: syntax error No: syntax error No: syntax error Works PostgreSQL 16.4 No: syntax error No: syntax error No: syntax error Works MySQL 8.0.46 No: error 1064 No: error 1064 No: error 1064 Works MySQL 9.5.0 Parsed, then error 6037: needs the hypergraph optimizer No: error 1064 No: error 1064 Works MariaDB 10.2.21 (old release) No: error 1064 No: error 1064 No: error 1064 Works MariaDB 12.3.3 (the `mariadb:lts` image) No: error 1064 No: error 1064 No: error 1064 Works Oracle AI Database 26ai Free (23.26.2.0.0) Yes Yes No: ORA-00936 Works SQLite 3.46.1 No: syntax error No: syntax error No: syntax error Works “Bare” means `GROUP BY ALL` with no column list, the shorthand for “group by every non-aggregated column in the `SELECT`”. SQL Server has an older, different `GROUP BY ALL column` form, covered [below](#sql-server). ### Not tested by us: read from vendor documentation We did not run these. Each cell is what the linked Microsoft, Snowflake, Google, DuckDB or Databricks page said when we opened it on 17 October 2026. Database `QUALIFY` `GROUP BY ALL` `ORDER BY ALL` Source Microsoft Fabric Warehouse and SQL analytics endpoint Documented; Microsoft lists it as preview Documented Documented [T-SQL surface area](https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area) (page dated 2026-09-16) and [QUALIFY](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-qualify-clause-transact-sql?view=fabric) (dated 2026-09-21) Azure Synapse Analytics Not listed: Microsoft’s QUALIFY page lists only Fabric under “Applies to” Not mentioned on the Synapse feature page [Synapse T-SQL features](https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/overview-features) Snowflake Documented Documented Documented [QUALIFY](https://docs.snowflake.com/en/sql-reference/constructs/qualify), [GROUP BY](https://docs.snowflake.com/en/sql-reference/constructs/group-by), [ORDER BY](https://docs.snowflake.com/en/sql-reference/constructs/order-by) BigQuery Documented Documented Not documented: no `ALL` in its ORDER BY section [Query syntax](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax) DuckDB (current documentation) Documented Documented Documented [QUALIFY](https://duckdb.org/docs/current/sql/query_syntax/qualify.html), [GROUP BY](https://duckdb.org/docs/current/sql/query_syntax/groupby.html), [ORDER BY](https://duckdb.org/docs/current/sql/query_syntax/orderby.html) Databricks SQL Documented Databricks Runtime 12.2 LTS and above Databricks Runtime 12.2 LTS and above [SELECT](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select), [GROUP BY](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-groupby), [ORDER BY](https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-select-orderby) Two notes. Microsoft lists “Warehouse T-SQL enhancements (Preview)” among the Fabric [features currently in preview](https://learn.microsoft.com/en-us/fabric/fundamentals/whats-new#features-currently-in-preview) (page dated 2026-10-02) and names `QUALIFY` there; that entry does not name `GROUP BY ALL` or `ORDER BY ALL`, which the surface-area page documents without a preview label. BigQuery’s query syntax page documents `QUALIFY` (“A window function is required to be present in the QUALIFY clause or the SELECT list”) and `GROUP BY ALL`, and the string “ORDER BY ALL” does not appear on it. ## What `QUALIFY`, `GROUP BY ALL` and `ORDER BY ALL` do - `QUALIFY` filters on the result of a window function, the way `HAVING` filters on an aggregate. It runs after `GROUP BY`, `HAVING` and the window functions, and before `ORDER BY`. The usual use is “first row per group”. - `GROUP BY ALL` infers the grouping columns from the `SELECT` expressions that have no aggregate function, so you do not repeat the column list. Which expressions are left out varies by engine: BigQuery also excludes window expressions and constants, and Oracle excludes window functions, scalar subqueries, constants and bind variables. - `ORDER BY ALL` sorts by every column in the `SELECT` list, left to right. ## How we tested All runs were on 17 October 2026 on an x86-64 Linux box, in Docker containers, with each engine’s own command-line client: `sqlcmd`, `psql`, `mysql` or `mariadb`, SQL\*Plus and `sqlite3` 3.46.1. The table lived in a scratch table named `blogp2_sales` (in `tempdb` on SQL Server) and was dropped afterwards. - SQL Server 2025 RTM-CU8 (17.0.4075.5), `mcr.microsoft.com/mssql/server:2025-latest` - PostgreSQL 19 RC1 (`postgres:19rc1`, reported as `PostgreSQL 19rc1 (Debian 19~rc1-1.pgdg13+1)`); the PostgreSQL project announced RC1 on 15 October 2026 ([release announcement](https://www.postgresql.org/about/news/postgresql-19-rc-1-released-3409/)). PostgreSQL 16.4 from the PostGIS 16 image. - MySQL 8.0.46 and 9.5.0 (both Community Server), MariaDB 10.2.21 and MariaDB 12.3.3 (the `mariadb:lts` tag on the day). - Oracle AI Database 26ai Free, 23.26.2.0.0, service `FREEPDB1`. - SQLite 3.46.1. The dataset, identical everywhere (Oracle uses `VARCHAR2` instead of `VARCHAR`): ``` CREATE TABLE blogp2_sales (id INT, region VARCHAR(10), product VARCHAR(10), amount INT); INSERT INTO blogp2_sales VALUES (1,'north','pen',10); INSERT INTO blogp2_sales VALUES (2,'north','ink',30); INSERT INTO blogp2_sales VALUES (3,'north','pad',20); INSERT INTO blogp2_sales VALUES (4,'south','pen',5); INSERT INTO blogp2_sales VALUES (5,'south','ink',25); ``` The four statements: ``` -- Q1 QUALIFY SELECT region, product, amount FROM blogp2_sales QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) = 1 ORDER BY region; -- Q2 GROUP BY ALL SELECT region, SUM(amount) AS total FROM blogp2_sales GROUP BY ALL ORDER BY region; -- Q3 ORDER BY ALL SELECT region, product FROM blogp2_sales ORDER BY ALL; -- Q4 portable rewrite SELECT region, product, amount FROM (SELECT region, product, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM blogp2_sales) t WHERE rn = 1 ORDER BY region; ``` A successful Q1 and Q4 return `north | ink | 30` and `south | ink | 25`. A successful Q2 returns `north 60` and `south 30`. ## The exact errors, engine by engine ### SQL Server 2025 ``` Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'ROW_NUMBER'. -- Q1, QUALIFY Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'ORDER'. -- Q2, GROUP BY ALL Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'ALL'. -- Q3, ORDER BY ALL ``` The Q1 message points at `ROW_NUMBER` and not at `QUALIFY` most likely because T-SQL reads `QUALIFY` as a table alias, which makes the function call after it the first token that cannot be parsed. If you see Msg 102 on a `QUALIFY` query, the clause is the cause. **SQL Server already has a `GROUP BY ALL` with a different meaning.** `GROUP BY ALL region` (with a column) is a legacy form that keeps groups the `WHERE` removed, where excluded groups stay in the result: SUM returns NULL for them and COUNT returns 0 (we ran `SUM(amount)`, `COUNT(*)` and `COUNT(amount)`). On our data: ``` SELECT region, SUM(amount) AS total FROM blogp2_sales WHERE amount > 25 GROUP BY ALL region; -- north 30 -- south NULL SELECT region, SUM(amount) AS total FROM blogp2_sales WHERE amount > 25 GROUP BY region; -- north 30 ``` That is why a query written for DuckDB or Snowflake can fail with a syntax error at the next keyword instead of a clear “not supported” message: SQL Server expects a column name after `ALL`. Microsoft documents `QUALIFY`, bare `GROUP BY ALL` and `ORDER BY ALL` for the SQL analytics endpoint and Warehouse in Fabric, not for SQL Server 2025 (see the table above). ### PostgreSQL 16.4 and 19 RC1 Identical on both: ``` ERROR: syntax error at or near "ROW_NUMBER" -- Q1, QUALIFY ERROR: syntax error at or near "ORDER" -- Q2, GROUP BY ALL ERROR: syntax error at or near "ALL" -- Q3, ORDER BY ALL ``` PostgreSQL does parse `GROUP BY ALL region`, because [its syntax](https://www.postgresql.org/docs/16/sql-select.html) is `GROUP BY [ ALL | DISTINCT ] grouping_element`, where `ALL` is the default and changes nothing. The bare shorthand that infers the columns is what fails. PostgreSQL 19 had that shorthand for a while. The commit “Add GROUP BY ALL” was reverted on 2026-07-17 ([pgsql-committers message, 2026-07-17](https://www.postgresql.org/message-id/E1wkox6-000fl4-1F%40gemulon.postgresql.org)). The commit message says a review found wrong results when an `ORDER BY` used non-default equality semantics, and that fixing it cleanly was too much churn for late beta. The [Beta 3 announcement](https://www.postgresql.org/about/news/postgresql-186-1711-1615-1519-1424-and-19-beta-3-released-3365/) (2026-08-13) lists “Revert GROUP BY ALL” under changes since Beta 2. The 19 RC1 release notes list no `GROUP BY ALL`, `QUALIFY` or `ORDER BY ALL` entry ([release notes](https://www.postgresql.org/docs/19/release-19.html), checked 17 October 2026), and our run on RC1 confirms it. Our [PostgreSQL 19 article](/blog/whats-new-postgresql-19/) covers the other beta reverts. ### MySQL 8.0 and 9.5, MariaDB 10.2 and 12.3 MySQL 8.0.46 and both MariaDB versions answer all three with error 1064, for example: ``` ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROW_NUMBER() OVER (PARTITION BY ... ERROR 1064 (42000): ... near 'ALL ORDER BY region' at line 1 -- GROUP BY ALL ERROR 1064 (42000): ... near 'ALL' at line 1 -- ORDER BY ALL ``` MySQL 9.5.0 is different for `QUALIFY` only. The parser knows the clause, and the server then refuses to run it: ``` ERROR 6037 (HY000): 'QUALIFY clause' can be used only if the hypergraph optimizer is enabled. mysql> SET optimizer_switch='hypergraph_optimizer=on'; ERROR 3999 (42000): The hypergraph optimizer does not yet support 'use in non-debug builds' ``` On the MySQL Community Server 9.5.0 build we ran, `QUALIFY` is therefore parsed but not usable. We did not try a debug build or MySQL HeatWave. `GROUP BY ALL` and `ORDER BY ALL` still fail with 1064 on 9.5.0. MariaDB 10.2.21 is an old release and is in the table because it is the oldest version in our test set; 12.3.3 behaves the same way. ### Oracle AI Database 26ai Free Q1 and Q2 ran and returned the expected rows. Q3 failed: ``` SELECT region, product FROM blogp2_sales ORDER BY ALL * ERROR at line 1: ORA-00936: missing expression ``` Oracle’s SQL language reference for 26ai documents the `QUALIFY` clause and `GROUP BY ALL` ([SELECT](https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/SELECT.html), checked 17 October 2026). Its notes on `GROUP BY ALL`: `ALL` is a reserved word, it cannot be combined with `ROLLUP`, `CUBE` or `GROUPING SETS`, and it leaves out constants, bind variables, scalar subqueries and window functions. Oracle’s New Features guide lists `GROUP BY ALL` under [Release Update 23.9](https://docs.oracle.com/en/database/oracle/oracle-database/26/nfcoa/ru23_9.html) and “Support for the QUALIFY Clause” under [Release Update 23.26.0](https://docs.oracle.com/en/database/oracle/oracle-database/26/nfcoa/ru23_26_0.html) (both pages checked 17 October 2026). A 19c or 21c server does not have either; our [Oracle 26ai article](/blog/2026-08-27-whats-new-oracle-26ai/) covers what else came in the 23.26 release updates. ### SQLite 3.46.1 ``` Parse error near line 6: near "ROW_NUMBER": syntax error -- Q1 Parse error near line 7: near "ALL": syntax error -- Q2 Parse error near line 8: near "ALL": syntax error -- Q3 ``` We tested the 3.46.1 shell shipped with our test box; we did not test newer SQLite releases. ## The portable rewrite Where `QUALIFY` is missing, compute the window function in a subquery or CTE and filter it outside. Q4 above ran unchanged on SQL Server 2025, PostgreSQL 16.4 and 19 RC1, MySQL 8.0.46 and 9.5.0, MariaDB 10.2.21 and 12.3.3, Oracle 26ai Free and SQLite 3.46.1, and returned `north | ink | 30` and `south | ink | 25` on each. Two things to watch when you convert: - **Ties.** `ROW_NUMBER() = 1` keeps exactly one row per group, chosen arbitrarily among ties. `RANK() = 1` or `DENSE_RANK() = 1` keeps all tied rows. `QUALIFY` does not change which of these you get; the function you wrote decides. - **The alias.** Several engines that support `QUALIFY` let it refer to a window function alias from the `SELECT` list (Microsoft’s Fabric page shows `QUALIFY rn <= 3`). In the rewrite that alias becomes the column you filter with `WHERE` in the outer query, as `rn` does above. `GROUP BY ALL` and `ORDER BY ALL` have no equivalent other than writing the column list out. A script that writes `GROUP BY ALL` for DuckDB or Snowflake needs the explicit list on SQL Server, PostgreSQL, MySQL and SQLite. ## How Jam SQL Studio treats this syntax - **SQL Server connections.** Since version 1.5.5 the query editor recognizes `QUALIFY`, bare `GROUP BY ALL` and `ORDER BY ALL` so that Fabric Warehouse queries are not underlined. The server still decides: on SQL Server 2025 the query fails with the messages above. Once the editor has read that the server is not Fabric Data Warehouse, autocomplete badges these keywords as Fabric-only by default. - **Synapse serverless and the default settings.** Synapse serverless SQL pools report the same platform as Fabric, so nothing is badged there. With the version-filter setting that shows everything, no badges appear on any server, and with **Hide unavailable** the badged entries are hidden. - **Oracle connections.** `QUALIFY` is completed and colored, and a statement that ends in `GROUP BY ALL` runs on its own. - **PostgreSQL, MySQL and SQLite.** `qualify` is an ordinary identifier there, and nothing is suggested. Details are in the [Fabric Warehouse syntax section of the Query Editor docs](/docs/query-editor/#fabric-warehouse-syntax). ## Quick Answers Short answers to the common questions about QUALIFY and GROUP BY ALL support. ### Q: Does SQL Server support QUALIFY? A: Not SQL Server 2025. On SQL Server 2025 (RTM-CU8, 17.0.4075.5) a query with QUALIFY fails with Msg 102, Incorrect syntax near ROW\_NUMBER, most likely because the parser reads QUALIFY as a table alias. Microsoft documents QUALIFY for the SQL analytics endpoint and Warehouse in Microsoft Fabric only. On SQL Server, put the window function in a subquery or CTE and filter it with WHERE. ### Q: Does PostgreSQL support GROUP BY ALL? A: Not the shorthand that groups by every non-aggregated column. PostgreSQL 16.4 and 19 RC1 both answer that bare GROUP BY ALL with a syntax error at ORDER, the next keyword. PostgreSQL does accept ALL as a modifier before the grouping elements, as in GROUP BY ALL region, which is the default and changes nothing. The shorthand was committed for PostgreSQL 19 and reverted on 2026-07-17 during the beta, so it is not in the 19 release candidate. ### Q: Which databases support QUALIFY? A: Of the databases we ran: Oracle AI Database 26ai only. From their documentation: Snowflake, BigQuery, DuckDB, Databricks SQL and Microsoft Fabric Warehouse. SQL Server 2025, PostgreSQL 16 and 19 RC1, MySQL 8.0, MariaDB 10.2 and 12.3 and SQLite 3.46 reject it. MySQL 9.5 parses it and then refuses it unless the hypergraph optimizer is on, which the MySQL Community Server 9.5.0 build we tested does not allow. ### Q: How do I write QUALIFY on a database that does not have it? A: Compute the window function in a subquery or CTE and filter it in the outer WHERE. For the first row per group: SELECT region, product, amount FROM (SELECT region, product, amount, ROW\_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales) t WHERE rn = 1. It ran unchanged on all nine server versions we tested. ### Q: What does GROUP BY ALL mean in SQL Server? A: In SQL Server, GROUP BY ALL region is an old form that returns every group, where a group that WHERE filtered out stays in the result and SUM returns NULL for it. It is not the shorthand that groups by every non-aggregated column. In our test it returned south with a NULL total for a group the WHERE had removed. The bare GROUP BY ALL with no column list fails on SQL Server 2025. ## Summary For code that has to run on several engines, treat `QUALIFY`, `GROUP BY ALL` and `ORDER BY ALL` as unavailable and use the subquery rewrite and explicit column lists. Oracle 26ai takes the first two, MySQL 9.5 parses `QUALIFY` but the Community Server 9.5.0 build we tested cannot run it, and the warehouse engines we read about (Fabric Warehouse, Snowflake, DuckDB, Databricks, BigQuery for two of the three) document them. This page was checked on 17 October 2026; PostgreSQL 19 final, a newer MySQL or an Oracle release update can change a row. ### Related - [Query Editor: Fabric Warehouse Syntax](/docs/query-editor/#fabric-warehouse-syntax) - [What’s New in PostgreSQL 19](/blog/whats-new-postgresql-19/) - [What’s New in Oracle 26ai](/blog/2026-08-27-whats-new-oracle-26ai/) - [Autocommit, COMMIT and ROLLBACK in 5 SQL Databases](/blog/2026-10-14-autocommit-commit-rollback-by-database/) ## QUALIFY and GROUP BY ALL Autocomplete in Jam SQL Studio The Jam SQL Studio query editor recognizes QUALIFY, GROUP BY ALL and ORDER BY ALL on SQL Server connections, so Fabric Warehouse queries are not underlined. With the default version-filter setting, once it has read that the server is not Fabric Data Warehouse, autocomplete badges them as Fabric-only. Oracle connections complete QUALIFY. [Download Jam SQL Studio Free](/#download) [Fabric Warehouse syntax in the docs](/docs/query-editor/#fabric-warehouse-syntax) Free for personal use • No account required • Mac, Windows, Linux ![Jam SQL Studio Query Editor with a SQL script and the results grid below it](/images/quick-tour/ph-query-editor.png)