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

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.

Answer 2

Works in MySQL.

Answer 3

Works in postgres

Answer 4

http://en.wikipedia.org/wiki/Select_%28SQL%29#Result_limits 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

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

Answer 6

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.

Answer 7

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.