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
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