MySQL has a nice feature (although non standard) which allow to query resultsets' limit, offset as
SELECT * FROM TABLE LIMIT M, N;
Is it created by MySQL? or Postgres?
views:
211answers:
5
+4
A:
Update
I just stumbled across a Wikipedia page about Rasmus Lerdorf (the original creator of PHP) where I found this quote:
He has contributed to the Apache HTTP Server and he also came up with the LIMIT clause and added it to the mSQL Database in 1995. It is the origin of the LIMIT clauses found in MySQL and PostgreSQL.
So I think the answer to your question is Rasmus Lerdorf invented it, and mSQL was the first database to use it.
Original answer
Nearly every database has had a feature to limit the number of results returned, although the syntax varies from system to system.
On Wikipedia there is a list of a variety of databases and how they implement limiting the result set. I'm not sure which one of those was first but perhaps Ingres? Of those implementing something similar to offset Interbase appears to be the first of those on that list. Of those supporting the exact LIMIT M, N syntax you mentioned, PostgreSQL may have been the first.
Note also that limiting is now part of the SQL:2008 standard using the following syntax:
SELECT * FROM T FETCH FIRST 10 ROWS ONLY
Mark Byers
2010-06-12 14:04:28
To be fair `FETCH FIRST 10 ROWS ONLY` just sucks compared to `LIMIT 10`.
doublep
2010-06-12 14:09:33
Standard SQL sucks. I guess it's a tradition.
Mark Byers
2010-06-12 15:06:40
@Mark Byers - Clearly the people that built VB must consulting with the ISO people on SQL standards given how verbose that is.
Thomas
2010-06-12 15:49:58
@Thomas, Microsoft built VB and Microsoft's T-SQL is nice with TOP N. Someone else is behind this.
iconiK
2010-06-12 19:29:22
@Thomas - worse, I think it's the ANSI J4 COBOL committee <shudder!>
Bob Jarvis
2010-06-12 21:23:20
Such redundant syntax. Why `FETCH`? What else would you do with the results of a `SELECT`? Why `ROWS`? What else would you store in a table? Why `ONLY`? As if an SQL implementation would accept your limit clause and give you the full data set anyway.
dan04
2010-07-03 19:39:21
+5
A:
Postgres added the LIMIT syntax in v6.5, released on June 9th, 1999.
Based on the documentation, MySQL had LIMIT syntax starting at v3.23 (production release Jan, 2001). But the docs in the URL are for 4.1, which wasn't released until 2004.
SQL Server didn't have TOP until SQL Server 2000, shipping in late 2000.
Oracle has had ROWNUM since Oracle 6, released in 1988. Scarier still, is that it can perform better in cases than ROW_NUMBER!
OMG Ponies
2010-06-12 18:03:39
LIMIT/OFFSET was added in PostgreSQL 6.5, and is fully documented there. You are linking to 6.3 documentation... (FWIW, 6.5 was released in June 1999, but that was still after MySQL added LIMIT)
Magnus Hagander
2010-06-13 10:23:36
A:
I know that Rdb (originally a DEC Corporation product, now available from Oracle) had LIMIT TO n ROWS back in 1991.
Share and enjoy.
Bob Jarvis
2010-06-12 19:22:57
+1
A:
Between PostgreSQL and MySQL, PostgreSQL copied the syntax LIMIT from MySQL (in v6.5), and added the OFFSET syntax (it may be that that was copied as well, but I think mysql only had the comma-syntax back then). It was then (7.2) changed to only allow "LIMIT foo OFFSET bar" because the MySQL syntax was unclear.
Magnus Hagander
2010-06-13 10:21:56
+3
A:
MySQL copied it from mSQL, which Rasmus Lerdorf lays claim to implementing: http://itc.conversationsnetwork.org/shows/detail3298.html
That's not to say mSQL was the first, but it looks like earliest of those mentioned so far.
Morgan Tocker
2010-06-13 17:55:40
+1 This I think is the correct answer to the question, not the answer the OP accepted.
Mark Byers
2010-07-11 08:33:20