< partition_by_clause >

將 FROM [ http://msdn.microsoft.com/zh-cn/library/ms177634.aspx ] 子句生成的結果集劃入應用了 ROW_NUMBER 函數的分區。若要了解 PARTITION BY 語法，請參閱 OVER 子句 (Transact-SQL) [ http://msdn.microsoft.com/zh-cn/library/ms189461.aspx ] 。

< order_by_clause >

確定將 ROW_NUMBER 值分配給分區中的行的順序。有關詳細信息，請參閱 ORDER BY 子句 (Transact-SQL) [ http://msdn.microsoft.com/zh-cn/library/ms188385.aspx ] 。當在排名函數中使用 <order_by_clause> 時，不能用整數表示列。

bigint

ORDER BY 子句可確定在特定分區中為行分配唯一 ROW_NUMBER 的順序。

A. 返回銷售人員的行號

                        SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 



FROM Sales.vSalesPerson



WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
                      

B. 返回行的子集

                        USE AdventureWorks;

GO

WITH OrderedOrders AS

(

    SELECT SalesOrderID, OrderDate,

    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'

    FROM Sales.SalesOrderHeader 

) 

SELECT * 

FROM OrderedOrders 

WHERE RowNumber BETWEEN 50 AND 60;
                      

C. 將 ROW_NUMBER () 與 PARTITION 一起使用

                        SELECT FirstName, LastName, ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode 

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;
                      

參考

幫助和信息