Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?

Question

Is there an ANSI SQL alternative to the MYSQL LIMIT keyword?

The LIMIT keyword limits the number of rows returned by a SELECT e.g:

SELECT * FROM People WHERE Age > 18 LIMIT 2;

returns 2 rows.

SELECT * FROM People WHERE Age > 18 LIMIT 10, 2;

returns 2 rows after the first 10.

Answer 1

this shows the different ways:

-- DB2
select * from table fetch first 10 rows only 
-- Informix 
select first 10 * from table 
-- Microsoft SQL Server and Access 
select top 10 * from table 
-- MySQL and PostgreSQL 
select * from table limit 10 
-- Oracle 
select * from (select * from table) where rownum <= 10

Answer 2

Not in SQL:1999.

There are two possible approaches you can use in later standards, with generally low levels of support in today's DBMSs.

In SQL:2008 you can use the DB/2 syntax:

SELECT * FROM things
ORDER BY smell
FETCH FIRST n ROWS ONLY

This only works for “LIMIT n” and not the extended “LIMIT m, n” offset syntax. In SQL:2003 you can use window functions, which can support the extended syntax but is a super PITA:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY smell) AS rn,
    FROM things
)
WHERE rn<=n -- or rn BETWEEN m+1 AND m+n

You will more usually use the DBMS-specific methods today.

Answer 3

see also http://en.wikipedia.org/wiki/Select_(SQL)#FETCH_FIRST_clause

SELECT * FROM T LIMIT 10 OFFSET 20 -- Netezza, MySQL, PostgreSQL (also supports the standard, since version 8.4), SQLite, HSQLDB, H2

SELECT * from T WHERE ROWNUM <= 10 -- Oracle (also supports the standard, since Oracle8i)

SELECT FIRST 10 * from T -- Ingres

SELECT FIRST 10 * FROM T order by a -- Informix

SELECT SKIP 20 FIRST 10 * FROM T order by c, d -- Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)

SELECT TOP 10 * FROM T -- MS SQL Server, Sybase ASE, MS Access

SELECT TOP 10 START AT 20 * FROM T -- Sybase SQL Anywhere (also supports the standard, since version 9.0.1)

SELECT FIRST 10 SKIP 20 * FROM T -- Interbase, Firebird

SELECT * FROM T ROWS 20 TO 30 -- Firebird (since version 2.1)

SELECT * FROM T
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY -- DB2

SELECT * FROM T
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY -- DB2 (new rows are filtered after comparing with key column of table T)

Answer 4

I don't believe so. All the databases that I'm aware of use vendor-specific keywords for that functionality.

Answer 5

HSQL/H2 uses LIMIT like MySQL

Answer 6

Adding to @jle's answer:

• SQLite supports LIMIT (MySQL/PostgreSQL)
• InterBase/Firebird support SELECT FIRST and SKIP (like Informix)

Also see Emulate MySQL LIMIT clause in Microsoft SQL Server 2000

Answer 7

Let me link here a related SO question, with a great answer by Lukas Eder and another good answer by bobince:

How universal is the LIMIT statement in SQL?

edit: A few more good reference links, worth to look at in similar cases:

• Wikipedia on limiting results and FETCH FIRST in the SELECT statement ;
• "Comparison of Different SQL Implementations" on limiting the result sets ;
• The jOOQ user manual page on the LIMIT clause from Lukas Eder's answer.