Re: Checking for Sequential Numbers

Sorry, mistake. It should read

select --+ all_rows

        a.id + 1
from

        id_tbl a, id_tbl b
where a.id + 1 = b.id (+)
and b.id is null
order by a.id
/

and just drop the last result line (this may be faster than an additional condition "and a.id < (select max (u.id) from id_tbl u)" but dirtier).

Martin

Martin Haltmayer wrote:
>
> Try
>
> select --+ all_rows
> a.id
> from
> id_tbl a, id_tbl b
> where a.id >= 2
> and a.id + 1 = b.id (+)
> and b.id is null
> /
>
> Martin
>
> 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 Thu Jan 06 2000 - 12:40:44 CST