CODEWITHSUNDEEP
sql-server
Partha
Partha

Reputation: 2192

Row_Number() in SQLServer

select 
   sp_una_stl_key, 
   row_number() over(order by sp_una_stl_key)as stl_key 
from        
    t_unit_data_archive
where 
    stl_key>=10

This query is not executed, throws,

Msg 207, Level 16, State 1, Line 2 Invalid column name 'stl_key'.

i could not understand what is the problem. please help me!

Upvotes: 3

Views: 582

Answers (3)

HLGEM
HLGEM

Reputation: 96640

you can't use the aliased field in the where clause. This should work:

select * from 
(select sp_una_stl_key, row_number() over(order by sp_una_stl_key)as stl_key 
from t_unit_data_archive) a
where stl_key>=10

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 135171

another way although I would prefer CTE

select * from (select 
   sp_una_stl_key, 
   row_number() 
   over(order by sp_una_stl_key)as stl_key 
from        
    t_unit_data_archive) x
where 
    stl_key>=10

Upvotes: 1

marc_s
marc_s

Reputation: 755361

You can't use the ROW_NUMBER directly - you need to package it inside a Common Table Expression like this:

with CTE as
(
  select 
     sp_una_stl_key, row_number() over(order by sp_una_stl_key) as stl_key 
  from 
     t_unit_data_archive
)
select *
from CTE
where stl_key >= 10

Marc

Upvotes: 8

Related Questions