Scott Yang's Playground

The question is, is there a MySQL/PostgreSQL equivalent of LIMIT clause in Firebird? It is very useful in situations where you don’t get to keep the database cursor over multiple requests, i.e. in some web applications. In my scenario, I just want to the database to return the most relevant n rows of result, over a potential huge result set.

You used to need to write store procedures in Firebird to simulate LIMIT clause, but due to popular demand it has been included in Firebird 1.0. But Google did not yield useful results on the first page, except an relevant but somehow inaccurate information on IBPhoenix. Trial and error, and here’s what I found that was working with Firebird 1.0.3.

  SELECT FIRST x [SKIP y] ... [rest of query]

It will then return x rows of result from the query, and optionally skip first y rows. For example, returning row 21-30 from a query would be something like this in Firebird:

  SELECT FIRST 10 SKIP 20 column1, column2, column3 FROM foo

The same statement in MySQL would be:

  SELECT column1, column2, column3 FROM foo LIMIT 10, 20

Again in PostgreSQL:

  SELECT column1, column2, column3 FROM foo LIMIT 20, 10

And let’s not get into the differences between Oracle, MS SQL Server and other RDBSMS. What a wonderful world of SQL :)

By the way, while Firebird is such an excellent open source RDBMS for both the high-end transaction-safe applications and low end embedded database, its documentation is seriously lacking. I am still using the Interbase 6 Beta’s Language Reference PDF to consult SQL syntax, and there is no way I can know all the great things they have added to Firebird 1.0/1.5. All I want is the one-volume reference manual that provides everything a developer needs, in the same style of MySQL and PostgreSQL. Hopefully the documentation will pick up in speed, while the coders move towards releasing 1.5 and developing 2.0.