i am working on my project and i have to create a sql query to achieve my aims. but unfortunately i get an error when i run the query.
here is my Sql query :
select ROW_NUMBER()
OVER (ORDER BY Advertisement.AdsDate asc ) AS AdsIndex
,Advertisement.AdsId , Advertisement.AdsEnTitle from Advertisement
where Advertisement.Status=N'True' and AdsIndex<=17 and AdsIndex>=11
Error :
Invalid column name 'AdsIndex'.
How should i solve my problem ?
you can not get Rowno in Where clause.you have to cte or subquery or
with cte as
(
select ROW_NUMBER()
OVER (ORDER BY Advertisement.AdsDate asc ) AS AdsIndex
,Advertisement.AdsId , Advertisement.AdsEnTitle from Advertisement
where Advertisement.Status=N'True'
)
select * from cte where AdsIndex between 11 and 17
You can't reference a ROW_NUMBER in the where clause. See this relevant post:
SQL Row_Number() function in Where Clause
Try wrapping the query above in a CTE or subquery and filtering from there.
EDIT: More background here:
Use a subquery:
;with cte as(
select ROW_NUMBER() OVER (ORDER BY AdsDate asc ) AS AdsIndex,
AdsId,
AdsEnTitle
from Advertisement
where Status=N'True')
select * from cte
where AdsIndex<=17 and AdsIndex>=11
Or:
select * from (select ROW_NUMBER() OVER (ORDER BY AdsDate asc ) AS AdsIndex,
AdsId,
AdsEnTitle
from Advertisement
where Status=N'True') as cte
where AdsIndex<=17 and AdsIndex>=11
