Re: query issue

>
> select * from numbers where n_id in
> (select max(n_id) from numbers group by n_number);
>
>
> in your opinion is there any way to avoid the nested query?

There's no way, and frankly, no need to avoid the nested query. What you need to avoid is the double scan of the numbers table (or its index on N_ID). And I thought you said you need a single record, not the last record for every n_number?
Assuming that N_ID is unique, and the sequence generates unique numbers in ascending order (nocycle), this will select the very last record in the table using a NOSORT index access with a stopkey, and then a single table access by index ROWID (in other words: very fast):

select * from (
  select * from numbers order by n_id desc) where rownum=1;

Note that 'the very last' only refers to the order in which sequence numbers are generated, not necessarily the actual insertion order. There are questions like 'what counts as insertion time -- the time the insert statement is executed, or the time when the sequence number is generated?' that need to be answered -- in a legal debate, for example :-)

For all other intents and purposes, the sequence logic will do nicely. HTH,
Flado Received on Fri Jan 30 2004 - 15:54:32 CET