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.

Gauzy

Um... Those of us who know Ansi SQL may be able to help you if you told us what the MySql LIMIT keyword did... – Tuscany

James, SELECT * FROM table LIMIT x returns the first x results, and SELECT * FROM table LIMIT x, y returns y results with an offset of x. – Discuss

@Aistina: Huh. I didn't know the LIMIT x, y version -- very handy! – Lardaceous

Anyone know if which database abstraction layers support this and which do not (and for which DBs)? Answer for any language... – Hembree

LIMIT [x, ]y also works on Postgres. – Golding

Worth pointing out that the LIMIT x, y form still has to go over all the earlier rows, so it's not usually much faster than the other syntax (and can still be very slow for high limits). – Carmine

possible duplicate of How universal is the LIMIT statement in SQL? – Johnsiejohnson

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

Cirilla

I've always thought it was ridiculous that Oracle makes you do a subselect to do it this way, but the rownum property is assigned prior to reordering with ORDER BY. – Distress

'oraclese' has its pros and cons eh? I do like Oracle's join shorthand (+) for an outer join... – Cirilla

+1 nice summary! SQLite supports LIMIT, like MySQL/PostgreSQL. InterBase/Firebird support SELECT FIRST and SKIP, like Informix. – Sauncho

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.

Golding

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)

Shepp

Answer 4

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

Assay

Answer 5

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

Sauncho

Answer 6

HSQL/H2 uses LIMIT like MySQL

Sonnier

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.

Magneto