I am on Firebird 1.5.4.

I need to get a field value value dependant on the maximum of another
field value.

For example in the following case, I want the BATCH NUMBER value for
the maximum RECORDNO value.

I have tried the following SQL, but I keep on getting an error
message (TERMID is an input variable):

FOR SELECT MAX(RECORDNO), BATCHNUM FROM BATCH
WHERE
TERMID = :TERMID
GROUP BY BATCHNUM
INTO
:TRECORDNO, :TBATCHNUM
DO
BEGIN
IF :TRECORDNO > :OLDRECNO THEN
BEGIN
:OLDRECNO = :TRECORDNO;
:OLDBATCHNO = :TBATCHNO;
END
SUSPEND;
END

BATCHNUM = :OLDBATCHNO + 1;
IF (SWITCH = 0) THEN
BEGIN
IF (BATCHNUM > 999999) THEN
BEGIN
BATCHNUM = 1;
END
END

IF (SWITCH > 0) THEN
BEGIN
IF (BATCHNUM > 999) THEN
BEGIN
BATCHNUM = 1;
END
END



[Non-text portions of this message have been removed]