[ACCEPTED]-Select subset of rows using Row_Number() -row-number

Accepted answer
Score: 14

Use your query as subquery like bellow:

select * from (
    Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as [RowNo]
    from customers
) t
where RowNo between 50 AND 60

You 3 can use CTE as well but whether to choose 2 one over another read Difference between CTE and SubQuery? and check execution 1 plan.

Score: 5

You need to do something like this:

;WITH PaginatingData AS
(
    Select id, name, ROW_NUMBER() OVER (ORDER BY id asc) as 'RowNo'
    from customers
)
SELECT *
FROM PaginatingData
where RowNo between 50 AND 60

Use a 9 CTE (Common Table Expression - sort of an 8 "inline view") as a "wrapper" so 7 that your RowNo becomes a valid column name.

As 6 an outlook - with SQL Server 2012, you'd 5 be able to write something like this:

SELECT 
    id, name
FROM 
    dbo.customers
ORDER BY
    id
OFFSET 50 ROWS
FETCH NEXT 10 ROWS ONLY

SQL 4 Server 2012 will have this ANSI SQL Standard 3 compliant notation to do paging directly 2 based on an ORDER BY clause. See this blog post (or tons of others) for 1 more info and more samples.

More Related questions