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.