Hello Bud,

> We have an application that executes this statement on a table having 2
> million records (PK is an integer type and indexed):
>
> SELECT MAX(ALL PK) FROM MyTable;
>
> It takes quite a long time - maybe 10 minutes or so.
>
> I've tried this in isql:
>
> SELECT FIRST 1 * FROM MyTable ORDER BY PK DESC;
>
> "SELECT FIRST 1" doesn't seem to limit the query, only the result. This

takes

> the same amount of time as the SELECT MAX (ALL PK)
>
> So... what's an efficient way to get the maximum PK that doesn't require
> scanning every record?

Creating a DESC index on it.

Why do you want to get the max number?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com