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?

Hindbrain

What version of Oracle does that work in? – Tharp

ORACLE 9i @ AlphaServer, my mistake, just tried it, doesn't work :( – Hindbrain

Answer 1

http://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows lists all of the major variants of the select command.

I believe the best way to do this is to use the SET ROWCOUNT command before your SELECT statement.

So, for you:

SET ROWCOUNT 1
SELECT %s FROM %s

Isleen

Answer 2

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

Tenderize

In SQL Server, using OFFSET and FETCH NEXT requires the use of an ORDER BY clause. – Isleen

@BoltBait: You're right, I keep forgetting this. Thanks! – Tenderize

So these statements all skip two elements and return one, right? With the obious exception of the statements titled "without offset". – Scape

I looked at it again and it looks to me like the lower block skips 1 and returns two. If this is not the case I would love an explanation. For example the last example should return three elements in the middle select and then return the last two of those in the outer select, right? – Scape

@JensSchauder: You're right, sharp eye. Will fix immediately. – Tenderize

Apropos the comment from @BoltBait: the notion of a row number is pretty incomplete unless we all agree on the row order. For that reason I always recommend using ORDER BY even if it’s not technically required. Otherwise you’re really asking for any rows. – Residentiary

Answer 3

LIMIT is very far from universal - out of major RDBMS, it's pretty much restricted to MySQL and PostgreSQL. Here is a detailed analysis of how this is done in many other implementations, including MSSQL, Oracle and DB2, as well as in ANSI SQL.

Photoflood

Answer 4

It's not at all universal. Actually I am surprised it is working for you in Oracle; it didn't used to be present. Normally Oracle users go for ROWNUM.

Every database has its own syntax for limiting results by row number. There are also two methods that are ANSI standard SQL:

1. FETCH FIRST. Derived from DB/2 and only made standard in SQL:2008, so very little DBMS support. Can't use an offset.

2. The windowing function SELECT ..., ROW_NUMBER() OVER (ORDER BY some_ordering) AS rn WHERE rn BETWEEN n AND m ... ORDER BY some_ordering. This is from SQL:2003 and has some (patchy, sometimes slow) support in newer DBMSs. It can use an offset or any other comparison function on the row number, but has the drawback of being appallingly ugly.

Here's a good overview of the tediousness you will have to deal with if you want cross-DBMS pagination support.

Stipulate

+1 for a really good link on cross-platform ways to express LIMIT. – Baronage

Answer 5

LIMIT is not part of the ANSI SQL standard as of the 1992 standard; I don't have a copy of any later standard to hand. Vendors' compliance with the standard is pretty vague at the best of times. For what it's worth, "LIMIT" is listed as a reserved word (meaning it can't legally be used as an identifier even in cases where it's not a keyword in the implementation).

Usufruct

Answer 6

It doesn't work on MSSQL (which uses SELECT TOP 10 * FROM Blah instead). That cuts out a significant portion of the DB market. I'm not sure about others.

Also, it's possible, though very unlikely, that your DB API will translate it for you.

Economy

Answer 7

Since it was mentioned in one of the answers that LIMIT and OFFSET is more or less limited to MySQL and PostgreSQL, I thought of pointing out that SAP HANA also supports LIMIT and OFFSET clause. But OFFSET without LIMIT is not permitted in the SAP HANA database.

Atonality