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

Solution 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

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