--- In firebird-support@yahoogroups.com, Tim <tim@...> wrote:

>
> My apologies to all who have been kind enough to answer my questions.
>
> I should have pointed out that I was aware that there were syntax
> errors, and that I would need to fix them. Your answers have been
> helpful in reminding me how variables and IF statements should be

structured.

>
> What i am more interested in, however, is how to solve the following

problem :

>
> "I need to get a field 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."
>
> If I try "SELECT MAX(RECORDNO), BATCHNO FROM BATCH then I have to use
> the GROUP BY clause. The problem with that is that I get a result set
> with multiple rows.
>
> What I would like is a singleton result set, which for the maximum
> RECORDNO will give me the corresponding BATCHNO.
>
> Any ideas?
>
> Thanks, and apologies once again
>
> Tim
>

Hi Tim,

SELECT FIRST 1 RECORDNO, BATCHNO
FROM BATCH
ORDER BY RECORDNO DESC

Adam