Alex Rivera | Logout

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

Asked 2009-02-27T15:04:26.940
40

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.

mysql database keyword ansi-sql
Edit
Report

1 Answer

15

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)
answered 2011-09-23T10:39:27.757

Your Answer