> > Why do you want to get the max number?
>
> It's a generated id. When the app starts we want the largest one,

and we keep

> incrementing for new records.
>

Bud,

*** DONT DO THAT ***

The first problem with that approach is that it is not
multi-transaction safe. If you have multiple users connecting to the
database, or even multiple connections in different threads inserting
records, you will eventually run into a problem.

Your second transaction will not see the fact that a particular ID has
been already inserted by a concurrent uncommitted transaction. In fact
if it has snapshot isolation, it will NEVER see that ID.

Don't use Doug's suggestion either. The problem with that approach is
that once a record has been updated by a transaction, no other
transaction can update that record until the first transaction has
committed or rolled back. Again transaction modes affect this
behaviour and Firebird does not support dirty writes in any form!

The correct way to fetch the next ID in SQL terms is called a
sequence. Firebird/Interbase predates the SQL standard with this
feature, so it was historically called a generator. A
generator/sequence is simply a 'counter'. If you have a table called
Foo and a generator called Gen_FooID, and you always got your value from

GEN_ID(Gen_FooID, 1)

Then you can be guaranteed that your transaction gets a unique value
for the primary key.

Thirdly, using a generator/sequence is a hell of a lot quicker and
less resource intensive than using a select max query even with a
descending index.

I suggest you take a read of the generator guide.
http://www.firebirdsql.org/manual/generatorguide.html

Adam