[comp.databases] need SQL for "WHat #'s < 1000 aren't in the table yet"

beverly@ai.cs.wisc.edu (Beverly Seavey) (06/20/91)

  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

jfr@locus.com (Jon Rosen) (06/21/91)

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


Simplest approach to this problem is to build an auxiliary table that
holds all the refIds from 1 to 1000 (this is pretty trivial).  Then
do:

      select refId from auxTable 
      where refId not in (select refId from mainTable)
        
This will do what you want.  If the list of refIds is too long for this
to be practical, I think there is a pretty weird join scheme that wil
do this, but I am still thinking about it.  Maybe I will solve it by
tomorrow.
 
Jon Rosen

PS -
 
Ahah! I have it!!! Boy is it UGLY!!!!!!

I believe the following will sort of give you what you want:
 
    select 'From ', refId+1 from mainTable
      where refId+1 not in (select refId from mainTable)
    union
    select 'To   ', refId-1 from mainTable
      where refId-1 not in (select refId from mainTable)
    order by 2,1
 
Test it out:  If mainTable has 1,3,4,5 and 9 in it,
the query should return:

    To    0
    From  2
    To    2
    From  6
    To    8
    From  10
 
I know it is ugly, but the info you need it there, right?
                  
:-) :-) :-)
 
Jon Rosen (totally grossed out that I came up with this answer :-)

L.Carl.Pedersen@dartmouth.edu (L. Carl Pedersen) (06/21/91)

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>