Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

EDIT: Fixed link (the [question]: link format no longer works?) and added the relevant bit from the link to the question itself.

share|improve this question
add comment

1 Answer

up vote 8 down vote accepted

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 }
share|improve this answer
1  
This is also what the Postgres manual claims: 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 m queries working in a cross RDBMS way. –  chucksmash Dec 14 '12 at 16:46
1  
SQL Server 2012 adds this syntax as well –  Paul White Dec 15 '12 at 19:03
    
@chucksmash Except on MySQL, of course. Because they don't like standards. (Ref: GROUP BY.) –  Aaron Bertrand Jan 30 at 19:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.