database - How universal is the LIMIT statement in SQL?

Question

I'm in the process of generalizing a Django DB replication app and it uses the statement:

SELECT %s FROM %s LIMIT 1

to fetch 1 row and use the Python DBAPI to describe the fields, it works fine with ORACLE and MySQL but, how cross platform is the LIMIT statement?

Answer 1

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 ;-)