[ACCEPTED]-Select subset of rows using Row_Number() -row-number
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.
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