Not so easy.

I no need a UNIQUE value (this is the reason of a PK),
i need a second "human" sequence, rising from 1 for every record in these year.

With a Unique quey index (or a simplex index) with TWO fields, looks never can go to the last value, always run through some values.

--- In firebird-support@yahoogroups.com, "Parzival" <parzival1969@...> wrote:
>
> Hello,
>
>
>
> are you actually looking for an unqiue key or number? Like an unique order
> number? If yes, is a generator an option?
>
>
>
> If it's not go for the recommendation of fabianoaspro and put ROWS 1 in the
> select statement. This gives you only one record.
>
>
>
> Niko
>
>
>
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] Im Auftrag von skander_sp
> Gesendet: Dienstag, 16. April 2013 09:58
> An: firebird-support@yahoogroups.com
> Betreff: [firebird-support] AGAIN (i know, but different) Max optimized...
> or not?
>
>
>
>
>
> Easy (and usual case)
>
> Simple table
>
> CREATE TABLE ORDENES (
> ID_ORDEN INTEGER NOT NULL /* Primary key - GenId */
> YEAR_ORDEN INTEGER NOT NULL /* Year of Orden */,
> N_ORDEN INTEGER NOT NULL /* Order in the Year */,
> ... /* no matter */
> );
>
> ALTER TABLE ORDENES ADD CONSTRAINT UK_ORDENES UNIQUE (YEAR_ORDEN,
> N_ORDEN)USING DESCENDING INDEX UK_ORDENES_YEAR_N_ORDEN;
>
> Now I need to access in the insert trigger, for next order to be assigned to
> N_ORDEN in the YEAR_ORDEN using
>
> select coalesce(max(o.n_orden),0)+1
> from ordenes o
> where o.year_orden=new.year_orden
> into new.n_orden
>
> IT WORK!!!! nice, BUT NOT OPTIMIZED, in the Performance Analisys it read
> (indexed) all the N_ORDEN in the table, not going to the first (given the
> descending order of the index)
>
> How can optimize the index/query?
> Or simply is not possible doing more?
>
> Tks in advance
>
>
>
>
>
> [Non-text portions of this message have been removed]
>