[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
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.