CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
George2
George2

Reputation: 45771

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 the 20th result). Any ideas how to write this query in tsql efficiently?

thanks in advance, George

Upvotes: 8

Views: 6074

Answers (3)

Somnath Muluk
Somnath Muluk

Reputation: 57656

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

Upvotes: 5

SeaDrive
SeaDrive

Reputation: 4292

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

Upvotes: 2

Daniel Vassallo
Daniel Vassallo

Reputation: 344311

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

Upvotes: 17

Related Questions