ROW_NUMBER() Sql error

Question

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 ?

Answer 1

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:

Row_Number Over Where RowNumber between

Answer 2

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

Answer 3

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