Great feature for Sql 2005--Row_number()分页

今天偶尔在网上发现2005的分页比2000有了明显的提高，使用Row_number()能够实现像在mysql中分页一样。心中的遗憾终于在2005中完成了。EXCITING...

examples on net:

select OID ,Pos from (select OID, ROW_NUMBER() OVER (order by OID) as Pos from directoryRights) as T where T.Pos > 1 and T.Pos < 10 

EXAMPLES MSDN:

USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0

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;