At 11:41 AM 13-11-01 +1300, you wrote:

>What is the syntax for Limit in select statement in Firebird?

It is not called LIMIT.

Here is the doc from the release notes:

SELECT [FIRST (<integer expr m>)] [SKIP (<integer expr n>)]

Retrieves the first m rows of the selected output set. The optional SKIP clause will cause the first n rows to be discarded and return an output set of m rows starting at n + 1. In the simplest form, m and n are integers but any Firebird expression that evaluates to an integer is valid. A identifier that evaluates to an integer may also be used in GDML, although not in SQL or DSQL.

Parentheses are required for expression arguments and optional otherwise.

They can also bind variables, e.g. SKIP ? * FROM ATABLE returns the remaining dataset after discarding the n rows at the top, where n is passed in the "?" variable. SELECT FIRST ? COLUMNA, COLUMNB FROM ATABLE returns the first m rows and discards the rest. [Variable binding is not fully tested yet.]

The FIRST clause is also optional, i.e. you can include SKIP in a statement without FIRST to get an output set that simply excludes the rows appointed to SKIP.

Available in SQL and DSQL except where otherwise indicated.

Examples:

SELECT SKIP (5+3*5) * FROM MYTABLE;

SELECT FIRST (4-2) SKIP ? * FROM MYTABLE;

SELECT FIRST 5 DISTINCT FIELD FROM MYTABLE;

A Gotcha with SELECT FIRST
This
delete from TAB1 where PK1 in (select first 10 PK1 from TAB1);

will delete all of the rows in the table. Ouch! the sub-select is evaluating each 10 candidate rows for deletion, deleting them, slipping forward 10 more...ad infinitum, until there are no rows left.

Beware!



All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________

[Non-text portions of this message have been removed]