Thks for the answer, but not the way i'm looking for

The MAX in a Descendent index (or only a field) is a good way, don't force anyway.

The problem is this not only a field, but two.

I can't use a second table, because my point is optimize the index and query, not add complexity to the database

--- In firebird-support@yahoogroups.com, Christian Mereles <chmereles@...> wrote:
>
> The MAX is forcing through all the records.
>
> select coalesce(max(o.n_orden),0)+1
> from ordenes o
> where o.year_orden=new.year_orden
> into new.n_orden
>
>
> You could create an intermediate table to keep N_ORDEN:
>
> CREATE TABLE YEAR_ORDEN (
> YEAR_ORDEN INTEGER NOT NULL,
> N_ORDEN SMALLINT NOT NULL,
>
> PRIMARY KEY (YEAR_ORDEN, N_ORDEN)
> );
>
>
> CREATE OR ALTER TRIGGER MANTENER_NORDEN FOR ORDENES
> ACTIVE BEFORE INSERT POSITION 0
> AS
> begin
> /* N_ORDEN, de tabla intermedia y uso optimo del indice */
> select Y.n_orden from YEAR_ORDEN Y
> where Y.year_orden = new.year_orden
> into new.n_orden;
>
> if (new.n_orden is null)
> then new.n_orden = 1;
>
> update or insert into year_orden (year_orden, n_orden)
> values (new.year_orden, new.n_orden + 1)
> matching (year_orden);
>
> end
>
>
> Regards.
>
> Christian
>
>
> [Non-text portions of this message have been removed]
>