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 2008 filtering
  • Will the SQL Server default dateformat/language affect my DateTime object toString rendering?
  • Why does SQL Server default XACT_ABORT to OFF? Can it be set to ON globally?
  • Formatting query data in HTML email from SQL Server 2008
  • JPA GenerationType.AUTO not considering column with auto increment
  • SQL Join on Table A value within Table B range
  • Any ideas?

    Thanks

  • SQL Server - select rows that match all items in a list
  • SQL Converting string MMM.YY to date
  • ROW_Count() To Start Over Based On Order
  • Reading SQL Varbinary Blob from Database
  • FreeText Query is slow - includes TOP and Order By
  • TSQL foreign keys on views?
  • 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.