LIMIT in SELECT statements in Firebird
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.
Links to This Article
Comments
Actually the PostgreSQL equivalent is:
SELECT column1, column2, column3 FROM foo LIMIT 10 OFFSET 20;
as you could see from here – http://www.postgresql.org/docs/8.1/static/sql-select.html#SQL-LIMIT .
Thanks Scott, it was indeed an useful tip.
I agree with you regarding Firebird documentation: it’s really really incomplete.
The community should realize how important is to have up2date docs: indeed is a major key to success.
Thanks for the perfect hint!
By the way, if you check this on a relativly big table (100k records), using indexes for your query, the result is confusing. While the query without ROWS or FIRST-SKIP runs in milliseconds (using the indexes), the query with the ROWS or FIRST-SKIP doesn’t use the indexes and takes 1-2 seconds to retrieve the result. It’s a bit annoying. Any comments?
$result=ibase_query($conn,”SELECT FIRST 10 SKIP 20 soid,itemno,quantity,unitprice,itemovdesc,itemreserved1,itemreserved2,itemreserved3 from SODET”);
it’s not work
Warning: ibase_query() [function.ibase-query]: Dynamic SQL Error SQL error code = -104 Token unknown – line 1, char 13 10 in C:\AppServ\www\phpfirebird\sodet.php on line 12
how fixed?
I need to make a query that involves 2 tables (the classic master-detail), I need all the records on the master table that match the where clausa but just the first record of the detail table, how can this be done with Firebird?
Thanks in advance
Add a comment
Gravatar is used. Email address is required but will not be displayed. Please keep your comment on topic. No spamming and/or bad language. First time poster will be moderated. Scott reserves the right to delete/edit your comments.
Playground is the personal weblog of Scott Yang, a Chinese-Australian Christian who works as a software developer in Sydney Australia.
i need the syntax of IF query in Firebird and also
join queries