22 Votes

Firebird: SELECT with LIMIT

Question by PC Control | Last update on 2021-05-13 | Created on 2017-02-24

I am using a Firebird database and I would like to only read out a part of a query result, not all records. In MySQL, I have always used the keyword LIMIT for this purpose:

SELECT col1, col2 FROM tab LIMIT 10;
SELECT col1, col2 FROM tab LIMIT 20, 10;

Accordingly, the first statement would return the first 10 data records, the second query would return rows 21 to 30.

Unfortunately, when using the same syntax in Firebird, I get the following error message:

SQLException
Context: Statement::Prepare
Message: isc_dsql_prepare failed
SQL error code = -104
Token unknown: LIMIT

When interpreting this "Token unknown" line, it seems as if Firebird does not know the word "LIMIT" at all. I also have a look at the list of reserved words in Firebird and indeed, LIMIT is not listed there.

Is there nevertheless any possibility to carry out something like a LIMIT query in Firebird?

ReplyPositiveNegative
2Best Answer2 Votes

There is a "LIMIT" in Firebird, but you have to use another syntax there.

In Firebird, the keywords FIRST and SKIP are used to perform a limited query. You can use those words like that:

SELECT FIRST 10 col1, col2 FROM tab;
SELECT FIRST 10 SKIP 20 col1, col2 FROM tab;

The first statement corresponds to your first MySQL example and is reading out the first 10 records. The second statement corresponds to your second example. This line is returning 10 data sets after 20 records has been skipped.

As you can see, the SKIP is optional and can be omitted in case you only want to have the first data records of the result without skipping any data.
Last update on 2021-05-13 | Created on 2017-02-25

ReplyPositive Negative
Reply

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.