ms sql row_number() function - won't let me use within the same statement

Question

I have a the following sql statement:

$sql = "select siteid, row_number() OVER (ORDER BY siteid) as rownum FROM nwsite WHERE rownum >= 4";

"rownum" works when i use the name outside of the query (e.g. in a foreach loop) but when it comes to using it as a WHERE clause it never works.

Any ideas?

Thanks

Answer 1

One option is to

• wrap your select statement into a subselect
• use the rownum alias in the outer query

SQL Statement

select *
from   (
         select siteid
                , row_number() OVER (ORDER BY siteid) as rownum 
         FROM   nwsite 
       ) q
where  rownum >= 4