DataBaser.Net: Offset and fetch

그뎌 생겼다. 다음의 예는 처음 10개의 row는 결과에 포함하지 않고, 11~ 15까지 5개 Row를 리턴한다.

select productid, name    
from adventureworks.production.product
order by name 
offset 10 rows
fetch next 5 rows only

즉, 다음의 예와 결과가 같다.

select productid, name 
from ( 
select row_number() over(order by name) as rowid, productid, name 
from adventureworks.production.product 
) x 
where rowid between 11 and 15 
order by rowid

처음 5개 row를 가져와라.

select productid, name    
from adventureworks.production.product
order by name 
--offset 10 rows
fetch first 5 rows only

이런식으로 변수사용도 가능하다.

use AdventureWorks
go

declare @numberofrows int = 10 -- number of rows you want on a page
declare @desiredpagenumber int = 5 -- page number

select *
from(
    select rownum = row_number() over (order by employeeid), *
    from humanresources.employee
) as a
where rownum > (@numberofrows * (@desiredpagenumber - 1)) 
and rownum <= (@numberofrows * (@desiredpagenumber - 1)) + @numberofrows
order by employeeid

select *
from humanresources.employee
order by employeeid
    offset (@numberofrows * (@desiredpagenumber - 1)) rows
fetch next @numberofrows rows only;

[edit]

참고자료 #

SQL Server Denali Server Side Paging