On Wednesday, August 22, 2001, at 06:55 PM, Helen Borrie wrote:

> Markus,
>
> At 07:27 PM 22-08-01 +0200, you wrote:
>> Hi all,
>>
>> in a trigger I have a select statement like
>>
>> select Key, Field from Tbl where ...
>> order by Field desc
>>
>> I want to store the value of "Key" in a variable of
>> the trigger. But only for the first record.
>>
>> I need to get the Key-Value of the record with Max(Field)
>> but unfortuantely Field is not unique and I cannot write
>> Select Key, Max(Field) from....
>>
>> I remember that there was an answer sometimes ago
>> that suggested to somehow use rdb$database in the select
>> statement to only get one single record.
>>
>> Can anybody help ?
>
> There isn't logically any way you can guarantee a unique value for Key
> in this query, since you might have more than one Key having the same
> value for Field.
>
> You could get the Key from an ordered set, through including some code
> that places the Key you are looking for first in the set, something like
>
> [...]

The unique value issue notwithstanding this might be a good place to try
the new Firebird FIRST keyword. The SQL would look something like:

select first 1 key, field from tbl where ... order by ... into :var1,
:var2.

-John