Thanks, for answering. even if I was posting this on the wrong list! Sorry
for that. I will be more careful next time. I was running this sql in a
TIBQuery and wanted to tell about this too. Didn't realised that I in fact
posted a sql specific question, after formulating my problem.

One question dough, didn't you mean SELECT *LAST* APP_DATETIME,
APP_SERVICEID ?

Is this pure SQL code? Or a special FireBird thing?

Thanks, Tom

----- Original Message -----
From: Helen Borrie
To: IBObjects@yahoogroups.com
Sent: Sunday, November 18, 2001 3:51 AM
Subject: Re: [IBO] performance question


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
_______________________________________________________

Yahoo! Groups Sponsor



___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.