Ok, you can see as you want, i had here values (I can send to you, and check).
The result is SAME PERFORMANCE in both cases, because a "MAX" over a indexed field, use the index, then give result without read all table.

The problem is a TWO FIELDS index like this, never can reduce his performance to one read, cause the index has the values ordered, and need to find the year first then the first one (descendant) value.

Tks anytime, more opinions make me see clear other ways, but no one is "perfect" one-only-read for all cases (years)

--- In firebird-support@yahoogroups.com, Christian Mereles <chmereles@...> wrote:
>
> Check the query, just read ONE REGISTER to return the MAX each year !!!.
>
> select first 1 n_orden + 1 from ordenes
> where year_orden = new.year_orden
> order by year_orden desc, n_orden desc
> into new.n_orden;
> /*-----------------------------------------------------------*/
>
> The query that you use does the same, returns the MAX, but read all records
> !!!.
>
> select coalesce(max(o.n_orden),0)+1
> from ordenes o
> where o.year_orden=new.year_orden
> into new.n_orden;
> /*-----------------------------------------------------------*/
>
> I thought that was the problem, read all the records for the MAX.
>
> Regards.
>
> Christian
>
>
> [Non-text portions of this message have been removed]
>