> I used to know SQL92 very well. There's no facility for doing anything > like LIMIT or OFFSET in it. You had to use your programming language to > work your way through all the results and skip the ones you didn't want. > > It is because there was no standard for this that each of the big SQL > implementations came up with their own syntax, and even now they still > haven't standardised on one. I think the syntax SQLite implements is by > far the simplest to understand, and I'm surprised that the other SQL > engines haven't all implemented it as well as maintaining their own. > > Simon.
SQLite, MySQL, & PostgreSQL do share commonality in this area and indeed there appears to descrepanices with LIMIT. Try building a generic SQL Database access tool, one of the main areas of frustration, Limits. Select one row from database. Derby: SELECT * FROM schemaTableName FETCH FIRST ROW ONLY HSQL: (Believe it does support post LIMIT now.) SELECT LIMIT 0 1 * FROM schemaTableName MSAccess: (Looks like no limit support.) SELECT * FROM schemaTableName MSSQL: SELECT TOP 1 * FROM schemaTableName MySQL, PostgreSQL, SQLite: SELECT * FROM schemaTableName LIMIT 1 Oracle: SELECT * FROM schemaTableName WHERE ROWNUM=1 danap.
