SQL Server OFFSET equivalent

I am using SQL Server 2008 Enterprise on Windows Server 2008. I want to select result from top 11 to top 20 (e.g. I am only interested in the 11th to 开发者_C百科the 20th result). Any ideas how to write this query in tsql efficiently?

thanks in advance, George

Unfortunately SQL Server does not offer anything similar to MySQL's OFFSET syntax. However, you may want to try using a derived table as follows:

SELECT some_field
FROM   (
          SELECT some_field, ROW_NUMBER() OVER (ORDER BY some_id) AS rownum
          FROM   table
       ) AS t
WHERE  t.rownum BETWEEN 11 AND 20

See following solution is applicable only for SQL Server 2012 onwards.

Limit with offset in sql server:

SELECT email FROM myTable
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

//offset - no. of skipped rows

//next - required no. of next rows

Assuming a sort field PK,

select top 10 * from MyTable
where PK not in (select top 10 PK from Mytable order by PK)
order by PK

Edit: here's a variant

select top 10 * from 
(select top 20 * from MyTable order by PK) as MySelection
order by PK desc