SQL Row_Number() function in Where Clause

Admin 1
Viewed 307568

I found one question answered with the Row_Number() function in the where clause. When I tried one query, I was getting the following error:

"Msg 4108, Level 15, State 1, Line 1 Windowed functions can only appear in the SELECT or ORDER BY clauses."

Here is the query I tried. If somebody knows how to solve this, please let me know.

SELECT employee_id 
FROM V_EMPLOYEE 
WHERE row_number() OVER ( ORDER BY employee_id ) > 0 
ORDER BY Employee_ID
analytic-functions tsql sql-server sql
10 Answers
Select * from 
(
    Select ROW_NUMBER() OVER ( order by Id) as 'Row_Number', * 
    from tbl_Contact_Us
) as tbl
Where tbl.Row_Number = 5
user_11340310

I feel like all the answers showing use of a CTE or Sub Query are sufficient fixes for this, but I don't see anyone getting to the heart of why OP has a problem. The reason why what OP suggested doesn't work is due to logical query processing order here:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE/ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP
  12. OFFSET/FETCH

I believe this contributes to the answer greatly, because it explains why issues like this one occur. WHERE is always processed before SELECT making a CTE or Sub Query necessary for many functions. You will see this a lot in SQL Server.

user_27784050 
 select salary from (
 select  Salary, ROW_NUMBER() over (order by Salary desc) rn from Employee 
 ) t where t.rn = 2
user_109260780
Related