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>