EC
3169
MySQLERRORNotableWindow FunctionsHIGH confidence

Window function result alias cannot be used in WHERE or GROUP BY

Production Risk

Low — syntax error; use a CTE.

What this means

A column alias assigned to a window function result cannot be referenced in WHERE, HAVING, or GROUP BY in the same query level, because window functions are evaluated after these clauses.

Why it happens
  1. 1Referencing a window function alias in WHERE, GROUP BY, or HAVING at the same SELECT level.
How to reproduce
trigger — this will error
trigger — this will error
SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM t1 WHERE rn <= 5;

expected output

ERROR 3169 (HY000): Column alias of a window function cannot be used here.

Fix

Wrap in a CTE or derived table

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

Why this works

The window function is fully evaluated in the inner query before the outer WHERE is applied.

What not to do

✕

Sources
Official documentation ↗

MySQL 8.0 — 3169 ER_WINDOW_INVALID_WINDOW_FUNC_ALIAS_USE

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

← All MySQL errors