$ ./guess_the_languageSQL & Databases
SQL & Databases · hard · #48

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;
  1. Add GROUP BY dept to the query
  2. PARTITION BY must come last
  3. ROW_NUMBER needs a HAVING clause first
  4. No windows in WHERE; use a subquery
Window functions are computed after WHERE, so filter on them in an outer query or CTE.
Play SQL & Databases ▸