mariadb-syntax-window-and-cte

name	mariadb-syntax-window-and-cte
description	Use when writing window functions like ROW_NUMBER, RANK, LAG, LEAD, computing running totals, top-N per group, percentile bucketing, gap-and-island queries, tree walks with recursive CTEs, hierarchy traversal, or migrating PostgreSQL or MySQL window-queries to MariaDB. Prevents the common mistake of using the GROUPS frame which is NOT supported in MariaDB, forgetting to terminate a recursive CTE, calling LAST_VALUE with the default frame and getting the current row instead of the partition tail, or assuming a CTE is always materialized. Covers window function OVER syntax with PARTITION BY, ORDER BY, and ROWS or RANGE frames (NEVER GROUPS), ranking functions (ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE), value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE), aggregates used as window functions, named windows via the WINDOW clause, non-recursive CTEs with WITH AS, recursive CTEs since 10.2.2, the CYCLE col_list RESTRICT clause since 10.5.2, and the max_recursive_iterations cap. Keywords: window function, OVER, PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, MEDIAN, PERCENTILE_CONT, PERCENTILE_DISC, frame, ROWS frame, RANGE frame, GROUPS frame not supported, EXCLUDE not supported, CTE, common table expression, WITH AS, recursive CTE, WITH RECURSIVE, max_recursive_iterations, CYCLE RESTRICT, top N per group, running total, gap and island, tree walk, hierarchy, organizational chart, how do I rank rows, how do I number rows in a group, my recursive query hangs, my LAST_VALUE returns the wrong row, GROUPS frame syntax error, unknown frame unit GROUPS
license	MIT
compatibility	Designed for Claude Code. Requires MariaDB 10.6-LTS, 10.11-LTS, 11.x, 12.x.
metadata	{"author":"OpenAEC-Foundation","version":"1.0"}

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.