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!
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
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
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
