What is ROW_NUMBER function and how to use it for paging?

PROBLEM:

What is ROW_NUMBER function and how to use it for paging?

SOLUTION:

The ROW_NUMBER function needs an OVER clause. In this example the OVER clause contains an ORDER BY clause. This just tells the ROW_NUMBER function in what order to generate the row numbers. It's possible to have multiple ROW_NUMBER functions in a single SELECT statement each with their own ordering

SELECT
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNumber,
employeeID, LastName, FirstName, City

FROM Employees;

The following example returns a subset of rows with numbers 50 to 60 inclusive in the order of the OrderDate. This is usefull for PAGING

WITH OrderedOrders AS

(

    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'

    FROM SalesOrderHeader

)

SELECT *

FROM OrderedOrders

WHERE RowNumber BETWEEN 50 AND 60;

The following using the ROW_NUMBER function with the PARTITION BY argument. The PARTITION BY argument is grouping the ORDER BY clause

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

FROM vSalesPerson

WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!