Thanks to all who send answers and ideas - Larry Ekins, Connor McDonnald,
Jeffery Stevenson.
JP
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jan Pruner
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego,
Hello,
I've a table mt ( mt1 NUMBER(10) NOT NULL ) with unique index on the mt1
column.
There's a sequence of numbers 1,2,3,4,6,7,8,10,11 ... in the mt1
Now, I want to get the smallest number which is not in mt1
(excluding min(mt1)-x of course) into XY (in my example is it n. 5
select MIN(a.mt1)+1 INTO XY FROM
(select mt1 FROM MT MINUS SELECT mt1-1 FROM MT) a;
is better, but still full scan ...
JP
On Mon 19. November 2001 12:40, you wrote:
Hello,
I've a table mt ( mt1 NUMBER(10) NOT NULL ) with unique index on the mt1
column.
There's a sequence of numbers
column.
There's a sequence of numbers 1,2,3,4,6,7,8,10,11
... in the mt1
Now, I want to get the smallest number which is not
in mt1
(excluding min(mt1)-x of course) into XY (in my
example is it n. 5).
When I use SELECT MIN(mt1)-1 INTO XY FROM MT WHERE
MT1 NOT IN (select mt1+1
from mt
Stevenson
Databeast Slayer
Medical Present Value, Inc.
Austin, TX
-Original Message-
Sent: Monday, November 19, 2001 5:40 AM
To: Multiple recipients of list ORACLE-L
Hello,
I've a table mt ( mt1 NUMBER(10) NOT NULL ) with unique index on the
mt1
column.
There's a sequence of numbers