12

Are there currently plans to standardize one best way of limiting the number of results returned by a query?

The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? lists the various ways to handle this behavior in different languages:

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

I don't play in DBs that often so I'm speaking from ignorance here, but it seems like this is a pretty important functionality - at least important enough that I scratch my head when I see it has been left up to vendors.

Community
  • 1
chucksmash
  • 515
  • 1
  • 5
  • 9

1 Answers1

20

The latest draft SQL standard that I could find on the internet (dated 21/12/2011) has the following available for use in a query expression:

<result offset clause> ::=
    OFFSET <offset row count> { ROW | ROWS }

<fetch first clause> ::=
    FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }
Philᵀᴹ
  • 30,570
  • 9
  • 75
  • 105
  • 5
    This is also what the Postgres manual claims: http://www.postgresql.org/docs/current/static/sql-select.html#SQL-LIMIT – a_horse_with_no_name Dec 14 '12 at 16:18
  • Ahh, very cool. I liked the LIMIT keyword keyword from MySQL and PSQL but I'm not married to it and it will be a relief to have my queries working in a cross RDBMS way. – chucksmash Dec 14 '12 at 16:46
  • 4
    @chucksmash Except on MySQL, of course. Because they don't like standards. (Ref: GROUP BY.) – Aaron Bertrand Jan 30 '14 at 19:01