Subject Re: [ib-support] select single row only
Author Helen Borrie

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

create trigger....
declare variable counter integer;
for select Key, Field from aTable
order by Something /* Something determines which row comes first */
into :key, :field do
if counter = 0 then
/* use the current value of :key in your update statement */
counter = :counter + 1;

Other than that, I can't think of anything...

If Field in this other table is some kind of serial number that the trigger is going to update, then you will have to think very hard about the multiple user aspects. Your transaction will have to get exclusive access to that row *before* it starts to do anything...

All for Open and Open for All
InterBase Developer Initiative ยท