At 04:41 AM 22/08/2007, Ed Dressel wrote:

>I have the following SQL statement:
>
> select * from ClientINfo
> where Client_Id in (Select Max(Client_ID) from ClientInfo)
>
>where Client_ID is the primary key of ClientInfo.
>
>But the SQL above is very slow (almost 2 minutes).

That's because the query is DAFT. The IN() predicate is is most
certainly not interchangeable with "=", which is the mistake you are
making here. MAX() returns one result per aggregate grouping, not a
set. Use IN() for comparing the search argument with a SET of values
and only then when there's no better way to do it.

If that is not silly enough...there is one an only one Client_ID in
the whole table that will be "equal to the maximum
Client_ID". Why? Because Client_ID is the primary key...which means
no Client_ID will be equal to any other Client_ID. What you *really*
want to know is which *one* is the highest!

Slap a descending index on Client_ID and just ask the question
directly (note the table identifier syntax, too, please):

select ci1.* from ClientINfo ci1
where ci1.Client_Id = (select max(ci2.Client_Id from ClientInfo ci2)

If you are using Fb 1.5 or higher, it's highly likely that the SELECT
FIRST syntax will do the same thing more efficiently for you - test
and find out. You still need that descending index.

select first 1 * from ClientInfo order by Client_Id desc

./heLen