CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Matthew
Matthew

Reputation: 867

Selecting row with ROW_NUMBER() window function

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?

Upvotes: 3

Views: 15577

Answers (2)

Nick
Nick

Reputation: 147146

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

Upvotes: 17

Liam Clarke
Liam Clarke

Reputation: 385

The WHERE clause is applied before the windowing is, so you can't refer to a windowing function in the WHERE clause. You need to use a subquery:

SELECT 
    grade_id, 
    sum(grade_amount) as total
FROM (
    SELECT
        Employee.Salary_Grade_Id as grade_id, 
        Salary_Grades.Grade_Amount AS grade_amount, 
        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) x
WHERE  rowCol = 1
GROUP BY grade_id;

I also moved your join predicate up into the FROM clause as a best practice to avoid accidental cartesian joins if the predicate Employee.Salary_Grade_Id = Salary_Grades.Grade_Id was ever removed by mistake.

Upvotes: 5

Related Questions