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

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

Answer 2

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 3

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 4

Adding to @jle's answer:

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

Also see http://stackoverflow.com/questions/216673/emulate-mysql-limit-clause-in-microsoft-sql-server-2000

Answer 5

HSQL/H2 uses LIMIT like MySQL