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

“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.

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.

Two notes. Microsoft lists “Warehouse T-SQL enhancements (Preview)” among the Fabric 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). 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 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). 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 (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, checked 17 October 2026), and our run on RC1 confirms it. Our PostgreSQL 19 article 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, 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 and “Support for the QUALIFY Clause” under Release Update 23.26.0 (both pages checked 17 October 2026). A 19c or 21c server does not have either; our Oracle 26ai article 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.

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.