Windowed functions can only appear in the SELECT or ORDER BY clauses

尝试做分页处理

select row_number over (orderby id asc) as rownum,*

from table

where rownum>=(@page*@pagesize-@pagesize) and rownum<=(@page*pagesize)

https://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server

Getting the total number of results and paginating are two different operations. For the sake of this example, let's assume that the query you're dealing with is

SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate

In this case, you would determine the total number of results using:

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

...which may seem inefficient, but is actually pretty performant, assuming all indexes etc. are properly set up.

Next, to get actual results back in a paged fashion, the following query would be most efficient:

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

This will return rows 1-19 of the original query. The cool thing here, especially for web apps, is that you don't have to keep any state, except the row numbers to be returned.

Windowed functions can only appear in the SELECT or ORDER BY clauses

You can work around that by placing the window function in a subquery:

select  invoice
,       rn
from    (
        select  Invoice
        ,       row_number() over(order by Invoice) as rn
        from    Table1
        ) as SubQueryAlias
group by
        invoice
,       rn