Selecting row with ROW_NUMBER() window function

Question

I'm currently writing a query for a process at my work. I am trying to get specific rows by applying the ROW_NUMBER() function, and then selecting the rows based on their row positioning. However, I get the following error:

SQL compilation error: Window function [ROW_NUMBER() OVER (ORDER BY EMPLOYEE.SALARY_GRADE_ID ASC NULLS LAST)] appears outside of SELECT, QUALIFY, and ORDER BY clauses.

The query:

SELECT Employee.Salary_Grade_Id, SUM(Salary_Grades.Grade_Amount) AS total, ROW_NUMBER() OVER(ORDER 
BY Employee.Salary_Grade_Id) AS rowCol FROM Employee, Salary_Grades 
WHERE (Employee.Salary_Grade_Id = Salary_Grades.Grade_Id AND rowCol = 1) GROUP BY 
Employee.Salary_Grade_Id;

What doesn't make sense is that when I remove the AND rowCol = 1, the query works with no errors. But with it, I get the error. What is going on here?

Answer 1

Your issue is that rowCol is an alias for a window function (ROW_NUMBER()) and they cannot appear in a WHERE clause. You can use a QUALIFY clause instead:

SELECT Employee.Salary_Grade_Id, 
       SUM(Salary_Grades.Grade_Amount) AS total, 
       ROW_NUMBER() OVER(ORDER BY Employee.Salary_Grade_Id) AS rowCol 
FROM Employee, Salary_Grades 
WHERE (Employee.Salary_Grade_Id = Salary_Grades.Grade_Id)
GROUP BY Employee.Salary_Grade_Id
QUALIFY rowCol = 1

Note that you should use explicit JOIN syntax and rewrite the query as

SELECT Employee.Salary_Grade_Id, 
       SUM(Salary_Grades.Grade_Amount) AS total, 
       ROW_NUMBER() OVER(ORDER BY Employee.Salary_Grade_Id) AS rowCol 
FROM Employee
JOIN Salary_Grades ON Employee.Salary_Grade_Id = Salary_Grades.Grade_Id
GROUP BY Employee.Salary_Grade_Id
QUALIFY rowCol = 1