Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!ncar!hsdndev!dartvax!L.Carl.Pedersen
From: L.Carl.Pedersen@dartmouth.edu (L. Carl Pedersen)
Newsgroups: comp.databases
Subject: Re: need SQL for "WHat #'s < 1000 aren't in the table yet"
Message-ID: <1991Jun21.150611.6821@dartvax.dartmouth.edu>
Date: 21 Jun 91 15:06:11 GMT
References: <1991Jun19.202633.19582@spool.cs.wisc.edu>
Sender: news@dartvax.dartmouth.edu (The News Manager)
Organization: Dartmouth College, Hanover, NH
Lines: 69

In article <1991Jun19.202633.19582@spool.cs.wisc.edu>
beverly@ai.cs.wisc.edu (Beverly Seavey) writes:

>   I need a SQL statement that will find the gaps in our numbering scheme,
>   e.
>   g.
>   something vaguely like
> 
> 	Find X such that X < 1000 and there does not exist a ref_ID = X

Was originally going to send this just to Beverly, but I'd like to see
more practical techniques in this group, and I thought others might
find this amusing.

Dunno what DBMS you are using, but below is one way to do this with
ORACLE.  In this example, I created a test table T as a stand-in for
your table.


SQL> create table u as select 1 id from dual;

Table created.

SQL> insert into u values(1);

1 record created.

SQL> insert into u select a.id from u a,u,u,u,u,u,u,u,u,u;

1024 records created.

SQL> update u set id = rownum;

1026 records updated.

SQL> delete from u where id > 1000;

26 records deleted.

SQL> create table t as
  2     select * from u
  3      where id not between 500 and 510 and
  4            id not in(356,127,780,940);

Table created.

SQL> select id from u minus select id from t;

        ID
----------
       127
       356
       500
       501
       502
       503
       504
       505
       506
       507
       508
       509
       510
       780
       940

15 records selected.

SQL>