Subject Re: [firebird-support] Generator
Author Martijn Tonies
Hello Olaf,

> my description was not good.
> I need the value in a stored procedure, not by inserting the data! By
> inserting I have a trigger. = gen_id(generator,1);
> I have a table with some records. Now I need the last 10 inserted records
> with an offset of 5 for example. The last ID is 100, I want to get the
> records (IDs) 95 to 86 in this case. If the offset is 10, I need the
> records
> 90 to 81 and so on.

Whenever you use a generator to generate IDs, it can happen that
there will be gaps. Either the transaction fails for a logical reason
or it fails for another reason (eg: network connection down just
after the INSERT but before the COMMIT).

So, you can have the last ID of 100, but no IDs until you reach 92
and then you have a couple and then you have another gap.

> To get the last ID I thought it is the best way to use the generator with
> the value 0, but the description warns about errors.

If you need the highest few, have you looked at using ORDER BY
with the FIRST command?

FROM yourtable
ORDER BY idcolumn DESC

With regards,

Martijn Tonies
Upscene Productions

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!