3

I tried to find the standard way to limit the number of return values of a select query, but I can not find it in the BNF. Every DBMS seems to define its own way. Is there a standard way? And if not, why is not worth to standardize it?

ceving
  • 21,900
  • 13
  • 104
  • 178
  • Possible duplicate of [this one](http://stackoverflow.com/questions/595123/is-there-an-ansi-sql-alternative-to-the-mysql-limit-keyword) here... – Peter Schneider Nov 06 '15 at 08:45
  • Many products implemented their own versions before the SQL standard specified the FETCH FIRST syntax. – jarlh Nov 06 '15 at 08:53

1 Answers1

8

It is standardized.

The SQL standard defines the following syntax:

select * 
from some_table
order by id
fetch first 42 rows only;

Alternatively to start at a different row than the first one: 

select * 
from some_table
order by id
offset 42
fetch first 42 rows only;

This was introduced in SQL:2008

However not every DBMS supports the standard for this. Actually no DBMS fully supports everything that is defined in the standard. Some ignore the standard more than others.

According to Wikipedia the following DBMS supporting this:

  • PostgreSQL (8.4)
  • Oracle 12c
  • IBM DB2
  • SQL Server 2012
  • HSQLDB 2.0
  • H2
  • CA DATACOM/DB 11
  • Sybase SQL Anywhere
  • EffiProz
  • Mimer SQL supports it too. – jarlh Nov 06 '15 at 08:51
  • MS SQL Server 2012 supports OFFSET/FETCH as well. You can read about both standard and vendor-specific ways of limiting rows here: https://en.wikipedia.org/wiki/Select_(SQL)#Limiting_result_rows – modal_dialog Nov 06 '15 at 08:52
  • I still can not find it in the BNF as a part of the `SELECT` statement. The `FETCH` occurs in the grammar as a part of the [data statement](http://savage.net.au/SQL/sql-2003-2.bnf.html#SQL%20data%20statement) which is part of a procedure, a trigger or a cursor, but not part of a basic `SELECT`. – ceving Nov 06 '15 at 09:21
  • 1
    @ceving: that is SQL:2003, the `fetch first...` was added in SQL:2008 –  Nov 06 '15 at 09:22
  • @a_horse_with_no_name Is the 2008 grammar anywhere in the web? – ceving Nov 06 '15 at 10:01
  • I should have read the Wikipedia article. It explains it quite well. – ceving Nov 06 '15 at 10:32