EC
3161
MySQLERRORNotableWindow FunctionsHIGH confidence

Window function used in invalid context

Production Risk

Low — syntax error; use a CTE or derived table to resolve.

What this means

A window function (such as ROW_NUMBER(), RANK(), LEAD(), LAG()) was used in a context where it is not permitted, such as in a WHERE clause, GROUP BY expression, or another window function argument.

Why it happens
  1. 1Window function in WHERE clause instead of a derived table or CTE.
  2. 2Window function used as an argument to another window function.
  3. 3Window function in GROUP BY or HAVING without a subquery.
How to reproduce
trigger — this will error
trigger — this will error
SELECT * FROM t1 WHERE ROW_NUMBER() OVER (ORDER BY id) = 1;

expected output

ERROR 3161 (HY000): Window function is only allowed in SELECT and ORDER BY clause.

Fix

Wrap in a derived table or CTE

Wrap in a derived table or CTE
WITH ranked AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM t1)
SELECT * FROM ranked WHERE rn = 1;

Why this works

CTEs allow window functions to be evaluated first, then filtered.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 3161 ER_INVALID_WINDOW_FUNC_USE

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MySQL errors