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]