Re: Query to find first missing integer in a field

From: Phil H <phil_herring_at_yahoo.com.au>
Date: Tue, 7 Apr 2009 20:25:52 -0700 (PDT)
Message-ID: <96191f66-dc0a-446a-a034-9fa33e1388fc_at_k2g2000yql.googlegroups.com>

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

Received on Tue Apr 07 2009 - 22:25:52 CDT