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

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.

  • SQL Server Script Generator CAST datetime values from hex
  • Cannot restore database with .mdf extension in sql server
  • Find what rows are locked for a given table and who is locking them in SQL Server
  • What are locking issues in OLAP?
  • Trying to search a field with or without dashes in a query?
  • Why is the carat '^' not working in LIKE expression in SQL Server
  • Any ideas?

    Thanks

  • how to alter table in ms sql server2014 using php
  • Table variable row limitation?
  • Join on several tables and return data which could or could not exist in one table?
  • Changing one datatype to another in all columns of SQL Server in Java
  • How do I properly access temp tables created in another database?
  • increase the SET Text Size in SQL Server 2008 R2
  • 5 Solutions collect form web for “ms sql row_number() function – won't let me use within the same statement”

    Because the where clause is evaluated before the window function row_number is computed, you can’t include that column in your where clause.

    You can structure the query like this:

    select siteid, rownum from (select siteid, row_number() OVER (ORDER BY siteid) as rownum FROM nwsite) nw WHERE rownum >= 4

    In this case the inner query is computed in its entirety and then passed to the outer query where the rownum column can be acted upon.

    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
    

    Try it like this:

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

    You can’t use the alias defined in the columns part on the same WHERE clause. The WHERE clause doesn’t have that information yet.

    You can also define the RowNum in a CROSS APPLY, which will allow you to reference it.

    select siteid, 
           RowNum
    FROM nwsite 
    CROSS APPLY (SELECT row_number() OVER (ORDER BY siteid)) CxA(rownum)
    WHERE rownum >= 4
    

    Use common table expression :

    with Result (field1, field2...,rn)
    as
    (select field1, field2..., rn=row_number() over (partition by field1 order by field 2)
    from yourTable)
    select * from result where rn<=3 ; -- top 3 in each group
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.