patternMajor
ANSI/ISO plans for LIMIT standardization?
Viewed 0 times
limitansiforisostandardizationplans
Problem
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:
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.
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 <= 10I 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.
Solution
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:
::=
OFFSET { ROW | ROWS }
::=
FETCH { FIRST | NEXT } [ ] { ROW | ROWS } { ONLY | WITH TIES }Code Snippets
<result offset clause> ::=
OFFSET <offset row count> { ROW | ROWS }
<fetch first clause> ::=
FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }Context
StackExchange Database Administrators Q#30452, answer score: 20
Revisions (0)
No revisions yet.