select threadid from
(
select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads
) as T
where T.Pos > 100000 and T.Pos < 100030
====================================== =====
If the data volume of the table cs_threads in it is very large, for example, hundreds of millions of records, then this method should be problematic
because, select threadid from
(
select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads
) as T
where T.Pos > 100000 and T.Pos < 100030
This statement takes out select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads, and then performs paging outside SQL. It has not been tested on SQL2005, because the original way of writing it on ORACLE is No, it’s better to write it like this in ORACLE: select threadid from
(
select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads a where a.pos<100030
) as T
where T.Pos > 100000
Source: blog.joycode.com/dotey/archive/2006/01/16/70493.aspx