SELECT * FROM BATCH B1
WHERE BATCH_OPEN = 1
AND NOT EXISTS (SELECT * FROM BATCH B2 WHERE B2.BATCH_OPEN =
B1.BATCH_OPEN AND B2.BATCHNO > B1.BATCHNO)

This will return all rows with the highest BATCHNO and BATCH_OPEN = 1.

Set

At 13:32 07.05.2003 +0200, you wrote:

>I need to select the maximum value from a table given a certain select clause.
>
>In Sybase, I would do something like the following :
>
> SELECT * FROM BATCH
> INTO
> #TEMPTABLE
> WHERE
> BATCH_OPEN = 1;
>
>This would create a temporary (in memory) table with the same structure as
>BATCH, with values defined by the WHERE clause.
>
>I could then say
> SELECT MAX(BATCHNO) FROM BATCH INTO @MAXBATCH;
>
>Where Maxbatch would be a variable
>
>And end off by saying
> SELECT * FROM BATCH WHERE BATCHNO = @MAXBATCH
>
>Which would give me a singleton result that contains all the data from the
>row where BATCH_OPEN = 1 and BATCHNO is the highest batch number in the
>temporary table.
>
>How could I accomplish the same thing in FB?