Re: Query to find first missing integer in a field

From: DeanB <deanbrown3d_at_yahoo.com>
Date: Wed, 8 Apr 2009 06:26:12 -0700 (PDT)
Message-ID: <8265e183-b8a1-4bbe-a7b7-04ed6d7df1ae_at_w40g2000yqd.googlegroups.com>



On Apr 7, 11:25 pm, Phil H <phil_herr..._at_yahoo.com.au> wrote:
> If you *must* travel this road, something like the following may do
> the trick. Good luck.
>
> SQL> create table foo (c1 integer);
>
> Table created.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> begin
>   2
>   3          for k in 1 .. 1000000 loop
>   4                  insert into foo values (k);
>   5          end loop;
>   6
>   7  end;
>   8  /
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:47.79
> SQL>
> SQL> delete from foo where c1 in (23, 36000, 710000);
>
> 3 rows deleted.
>
> Elapsed: 00:00:00.20
> SQL>
> SQL> commit;
>
> Commit complete.
>
> Elapsed: 00:00:00.00
> SQL>
> SQL> select
>   2          c1 + 1 missing_value
>   3  from
>   4          (
>   5          select
>   6                  c1,
>   7                  lead(c1, 1) over (order by c1) next_c1
>   8          from
>   9                  foo
>  10          )
>  11  where
>  12          next_c1 <> (c1 + 1);
>
> MISSING_VALUE
> -------------
>            23
>         36000
>        710000
>
> Elapsed: 00:00:02.21
>
> -- Phil

Phil - I considered that method, but it searches the entire table for holes. I was trying to figure out a way it would just fetch the first hole. I don't mean just returning the first of the three missing_values, that's not the point, but maybe there is some clever Oracle construct (such as FIRST?) that would stop its search after finding one hole. Clearly this could be done easily in a pl/sql procedure, but for me its a challenge/exercise to do it straight in SQL. -Dean Received on Wed Apr 08 2009 - 08:26:12 CDT

Original text of this message