mariadb-syntax-window-and-cte

Window functions and Common Table Expressions in MariaDB 10.6 LTS, 10.11 LTS, 11.x, and 12.x. All claims verified against mariadb.com/kb/en/window-functions-overview/, window-frames/, with/, and recursive-common-table-expressions-overview/ on 2026-05-19.

Quick Reference

• ALWAYS use ROWS or RANGE for frame units. NEVER use GROUPS : MariaDB does NOT implement it at any version through 12.x (PostgreSQL and MySQL 8.0+ only).
• ALWAYS write an explicit frame for LAST_VALUE, FIRST_VALUE, NTH_VALUE, SUM, AVG, COUNT, MIN, MAX when you want partition-wide results. The default frame when ORDER BY is present is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (a running window, NOT the full partition).
• ALWAYS terminate a recursive CTE with a depth guard (WHERE depth < N) or CYCLE col RESTRICT (10.5.2+). Without termination, max_recursive_iterations (default 1000) aborts the query with error 1969.
• ALWAYS write ORDER BY inside OVER() when using value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE). They have no defined behavior without it.
• ALWAYS use ROW_NUMBER() (not RANK) when you need a unique rank per row. RANK skips after ties (1, 1, 3, 4) ; DENSE_RANK does not skip (1, 1, 2, 3) ; ROW_NUMBER is always unique.
• NEVER assume a non-recursive CTE is materialized. The optimizer merges it into the outer query when optimizer_switch=derived_merge=on (the default). Use EXPLAIN to confirm : select_type=DERIVED means materialized, inline expansion means merged. Recursive CTEs are ALWAYS materialized.
• NEVER use EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS, or DISTINCT inside an aggregate-as-window. None are implemented in MariaDB.
• NEVER use NULLS FIRST or NULLS LAST in ORDER BY inside OVER() : not supported. NULL sorts first under ASC, last under DESC (the default).
• ALWAYS check version : recursive CTE needs 10.2.2+, CYCLE col RESTRICT needs 10.5.2+, WITHIN GROUP for PERCENTILE_CONT / PERCENTILE_DISC / MEDIAN needs 10.3.3+.

Version Matrix