--- In firebird-support@yahoogroups.com, Yossi Zaig <yle@n...> wrote:

> Hi.
>
> I have this Query:
>
> SELECT
> MAX("Treatments"."WorkStartDate") "WorkStartDate",
> MAX

("TreatmentDetails"."TreatmentDetailDate") "TreatmentDetailDate"

> FROM
> "Treatments" LEFT OUTER JOIN "TreatmentDetails"
> ON "Treatments"."TreatmentID" = "TreatmentDetails"."TreatmentID"
>
> Is there away to get the bigger Date of those two fields in one

fields and not I two as I did ?

>
> Best Regards.
> Yossi

Hello Yossi,

There are a few ways to do it. If it is a one off, you could convert
your query into a stored procedure,

SELECT
MAX("Treatments"."WorkStartDate") "WorkStartDate",
MAX("TreatmentDetails"."TreatmentDetailDate") "TreatmentDetailDate"
FROM
"Treatments" LEFT OUTER JOIN "TreatmentDetails"
ON "Treatments"."TreatmentID" = "TreatmentDetails"."TreatmentID"
INTO :F1, :F2;

IF (:F1 < :F2) THEN
BEGIN
MAXNUM = :F2;
END
ELSE
BEGIN
MAXNUM = :F1;
END

Otherwise it won't be particularily hard to write a UDF for the task.

SELECT FB_MAXOFROW(MAX("Treatments"."WorkStartDate") "WorkStartDate",
MAX("TreatmentDetails"."TreatmentDetailDate") "TreatmentDetailDate")
FROM
"Treatments" LEFT OUTER JOIN "TreatmentDetails"
ON "Treatments"."TreatmentID" = "TreatmentDetails"."TreatmentID"

etc

Keep in mind, selecting max is expensive as it normally involves a
table scan. Firebird Index will NOT help you UNLESS they are declared
as descending. Perhaps you may want to consider another table to
store the maximum value in in the case that this sort of query is run
frequently and the tables contain more than about 1000 records.

Hope that helps
Adam