Why does this fail, and what is the usual fix?
SELECT * FROM emp WHERE ROW_NUMBER() OVER ( PARTITION BY dept ORDER BY salary DESC ) = 1;
- Add
GROUP BY deptto the query PARTITION BYmust come lastROW_NUMBERneeds aHAVINGclause first- No windows in
WHERE; use a subquery
Window functions are computed after
Play SQL & Databases ▸
WHERE, so filter on them in an outer query or CTE.