Posted by Ferrarista XXL on 07/13/06 17:50

Mahesh BS wrote:

>Hello,
> I need to write a query to find out a set of missing number in a given
>sequence.
>Eg : a Column in some table has the following data
>Col1
>1
>2
>3
>4
>5
>6
>8
>9
>10
>Here I need to write a query to find out that number 7 is missing in the
>given sequence.
>One possible solution is by using any loop. But I am looking out if the same
>can be achieved using any query.
>Thanks in advance.
>Regards,
>Mahesh
>
>
>
after some tests, i am arrived to this:

select (a.col1 + 1)
from tab1 a
where not exists
(select 1
from tab1 b
where b.col1 = (a.col1 + 1))
and a.col1 not in
(select max(c.col1)
from tab1 c)
order by 1

try it!
fabio



--
.... per questo oggi si dice:"Davanti alla Rossa c'è solo la pista..."
f.

Correr, competir, eu levo isso no sangue, é parte de minha vida
Ayrton Senna da Silva

leva UNA MARCIA per rispondermi in privato

[Back to original message]