LIMIT has become quite popular with a variety of Open Source databases, but unfortunately, the fact is that OFFSET pagination has been about the least standardised SQL feature of them all, having been standardised as late as in SQL:2008.

Until then, the jOOQ user manual page on the LIMIT clause shows how the various equivalent statements can be formed in each SQL dialect:

-- MySQL, H2, HSQLDB, Postgres, and SQLite SELECT * FROM BOOK LIMIT 1 OFFSET 2 -- CUBRID supports a MySQL variant of the LIMIT .. OFFSET clause SELECT * FROM BOOK LIMIT 2, 1 -- Derby, SQL Server 2012, Oracle 12c, SQL:2008 standard -- Some need a mandatory ORDER BY clause prior to OFFSET SELECT * FROM BOOK [ ORDER BY ... ] OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY -- Ingres SELECT * FROM BOOK OFFSET 2 FETCH FIRST 1 ROWS ONLY -- Firebird SELECT * FROM BOOK ROWS 2 TO 3 -- Sybase SQL Anywhere SELECT TOP 1 ROWS START AT 3 * FROM BOOK -- DB2 (without OFFSET) SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY -- Sybase ASE, SQL Server 2008 (without OFFSET) SELECT TOP 1 * FROM BOOK

Now, these were all pretty straight-forward, right? Here comes the nasty part, when you have to emulate them:

-- DB2 (with OFFSET), SQL Server 2008 (with OFFSET), SELECT * FROM ( SELECT BOOK.*, ROW_NUMBER() OVER (ORDER BY ID ASC) AS RN FROM BOOK ) AS X WHERE RN > 2 AND RN <= 3 -- DB2 (with OFFSET), SQL Server 2008 (with OFFSET) -- When the original query uses DISTINCT! SELECT * FROM ( SELECT DISTINCT BOOK.ID, BOOK.TITLE DENSE_RANK() OVER (ORDER BY ID ASC, TITLE ASC) AS RN FROM BOOK ) AS X WHERE RN > 2 AND RN <= 3 -- Oracle 11g and less SELECT * FROM ( SELECT b.*, ROWNUM RN FROM ( SELECT * FROM BOOK ORDER BY ID ASC ) b WHERE ROWNUM <= 3 ) WHERE RN > 2

Read about the ROW_NUMBER() vs. DENSE_RANK() rationale here

Choose your poison ;-)