|Subject||Re: [ib-support] select single row only|
On Wednesday, August 22, 2001, at 06:55 PM, Helen Borrie wrote:
> 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,