3167
MySQLERRORNotableWindow FunctionsHIGH confidence
Derived window cannot redefine ORDER BY of base window
Production Risk
Low — syntax error.
What this means
A derived window specification attempts to define its own ORDER BY clause while also inheriting from a named window that already has an ORDER BY clause, which is not allowed.
Why it happens
- 1Using (named_window ORDER BY ...) when the named window already contains ORDER BY.
How to reproduce
trigger — this will error
trigger — this will error
SELECT RANK() OVER (w ORDER BY name) FROM t1 WINDOW w AS (PARTITION BY dept ORDER BY id);
expected output
ERROR 3167 (HY000): Cannot override ORDER BY of a named window that already has ORDER BY.
Fix 1
Remove ORDER BY from the derived window
Remove ORDER BY from the derived window
SELECT RANK() OVER w FROM t1 WINDOW w AS (PARTITION BY dept ORDER BY id);
Why this works
Use the base window ORDER BY as-is.
Fix 2
Define a base window without ORDER BY
Define a base window without ORDER BY
SELECT RANK() OVER (w ORDER BY name) FROM t1 WINDOW w AS (PARTITION BY dept);
Why this works
Base window without ORDER BY can be extended by derived windows.
What not to do
✕
Sources
Official documentation ↗
MySQL 8.0 — 3167 ER_WINDOW_NO_REDEFINE_ORDER_BY2
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev