mariadb-window-functions

Compute ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC) in a subquery or CTE, then filter rn <= N in the outer query. Window functions cannot appear in WHERE, GROUP BY, or HAVING in MariaDB.

ROW_NUMBER gives distinct 1..N values, arbitrarily breaking ties. RANK gives ties the same value and skips subsequent ranks (1,2,2,4). DENSE_RANK gives ties the same value with no gaps (1,2,2,3), which matters for tie-heavy top-N queries.

MariaDB evaluates window functions after WHERE, GROUP BY, and HAVING, and only allows them in the SELECT list and ORDER BY. Wrap the window computation in a subquery or CTE and filter on its alias in the outer query.

With ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the last row in frame is the current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the partition's true last value.

Yes, but ordering goes in WITHIN GROUP (ORDER BY expr), not in OVER. The OVER clause for MEDIAN, PERCENTILE_CONT, and PERCENTILE_DISC accepts PARTITION BY only; putting ORDER BY inside OVER is a parse error.

MariaDB does not support GROUPS frames, NULLS FIRST/NULLS LAST ordering, or EXCLUDE frame exclusion (rejected at runtime). RANGE frames with numeric or interval offsets require a single-column ORDER BY and do not support DATE/DATETIME arithmetic.