At 03:01 AM 18-11-01 +0100, you wrote:

>Hi all,
>
>Is there a way to speed the following query up?
>
>SELECT APP_DATETIME as LAST_APPOINTMENT, APP_SERVICEID as LAST_SERVICEID
>FROM APPOINTMENTS APP1
>WHERE APP_DATETIME = (SELECT MAX(APP_DATETIME)
> FROM APPOINTMENTS APP2
> WHERE (APP2.APP_INVITEDID = :PersonID) AND (APP1.APP_INVITEDID= :PersonID))
>
>Or is there another, better way to find the same information

You surely don't need to "double-match" the PersonID parameter.
You do need a descending index on APP_DATETIME.

With Firebird 1:

SELECT FIRST APP_DATETIME, APP_SERVICEID
FROM APPOINTMENTS
WHERE APP_INVITEDID = :PersonID
ORDER BY APP_DATETIME DESC

With "not Firebird 1":

CREATE PROCEDURE GET_LAST_SERVICE (
PersonID INTEGER)
RETURNS (
LAST_APPOINTMENT TIMESTAMP,
LAST_SERVICEID INTEGER)
AS
BEGIN
SELECT MAX(APP_DATETIME) FROM APPOINTMENTS
WHERE APP_INVITEDID = :PersonID
INTO :LAST_APPOINTMENT;
SELECT APP_SERVICEID FROM APPOINTMENTS
WHERE APP_INVITEDID = :PersonID
and APP_DATETIME = :LAST_APPOINTMENT
INTO :LAST_SERVICEID;
SUSPEND; /* if you are bringing it back with SELECT */
END

If you are bringing the sp result back into a dataset, SQL is:

SELECT LAST_APPOINTMENT, LAST_SERVICEID
FROM GET_LAST_SERVICE(:PersonID)

(This bit IS IBO-specific...)
If you are running this through a TIB_DSQL, omit the SUSPEND. SQL is:

EXECUTE PROCEDURE GET_LAST_SERVICE(:PersonID)
and read the Fields[] array after execution (not Params[]).

btw, pure SQL questions are OFF-TOPIC on this list - you should post them to ib-support. Thanks.

Helen

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