Re: Checking for Sequential Numbers

try

select id from id_tbl
where id >1
and id -1 not in (select id from id_tbl)

xw

Buck Turgidson wrote:
>
> I have a table that should have sequential numbers, and I want to spot any
> gaps.
>
> I have written the following query which works using a small test (see
> below), but is unacceptably slow in checking the 28,000 rows in the actual
> table. Can anyone suggest a better way?
>
> select
> a.id
> from id_tbl a
> where a.id > 1
> and not exists (select b.id
> from id_tbl b
> where b.id - a.id = -1
> and b.id = (select max(c.id)
> from id_tbl c
> where c.id < a.id))
>
> create table id_tbl (id number (6) not null);
>
> insert into id_tbl values (1);
> insert into id_tbl values (2);
> insert into id_tbl values (3);
> insert into id_tbl values (4);
> insert into id_tbl values (5);
> insert into id_tbl values (6);
> insert into id_tbl values (7);
> -- << missing
> sequence
> insert into id_tbl values (9);
> insert into id_tbl values (10);
>
> create index id_tbl_idx on id_tbl(id);
Received on Mon Jan 10 2000 - 16:36:03 CST