Home -> Usenet -> comp.databases.theory -> Re: SQL Query Question

Re: SQL Query Question

From: Philip Lijnzaad <lijnzaad_at_ebi.ac.uk>
Date: 11 May 2001 17:07:39 +0100
Message-ID: <u71ypvg984.fsf_at_sol6.ebi.ac.uk> 








> On Fri, 11 May 2001 11:24:09 GMT, "Charcoal" <who_at_cares.com> wrote:



>> Assume I have a table which is just a list of numbers (only 1 column). I
>> want to perform a query which shows every number under 100 which is NOT in
>> the table. How do I do this?
 




> An interesting question, ... offhand, I would not think this is


> possible without creating a full enumeration in another table, as

> others have said.




yes, I agree, but full enumeration is really what you *don't* want, because
chances are that there are thousands of missing numbers. Instead, it's more
useful to know what the brackets on the gaps are. This is more compact and
gives more insight.



Here are some tricks that may be handy (tested on Oracle, but should work for
any decent SQL-92 engine):



  create table foo (n number(4));
  

  insert into foo values(1);
  insert into foo values(2);
  insert into foo values(3);
  




  insert into foo values(5);


  insert into foo values(6);
  


  insert into foo values(8);


  insert into foo values(13);
  


Find the numbers bracketing the gaps:



    
  
  •   the numbers just below a 'gap'
  select distinct n from foo
  minus
  select distinct(f1.n)
  from foo f1, foo f2
  where f1.n +1 = f2.n
  =>
	 N

    
	 3
	 6
	 8
	13
  
  
  •   the numbers just above of a 'gap'
  select distinct n from foo
  minus
  select distinct(f1.n)
  from foo f1, foo f2
  where f1.n = f2.n +1
  =>
	 N

    
	 1
	 5
	 8
	13






This is not as nice as having the whole report in one nice table, which is
done as follows:



    
  
  •   a table of gaps: first column is the start of the gap, 
  
  •   second table is the end of the gap. 
  select f1.n +1  as lo, f1.n + min(f2.n - f1.n) -1 as hi
  from foo f1, foo f2
  where f2.n > f1.n
  group by f1.n
  having min(f2.n - f1.n) > 1
  order by f1.n
  =>






        LO	   HI


---------- ----------

	 4	    4
	 7	    7
	 9	   12





(You can of course also search for gaps of a specific length by saying 
 having min(f2.n - f1.n) = 42 instead of having min(f2.n - f1.n) > 1 



As you can see, the added advantage is that you miss the lowest and highest 
numbers of the whole table. 
 


Isn't SQL wonderful ! (I don't know if these tricks are widely known, so if
not, spread the word, and I don't object to it being acknowledged !-)




                                                                      Philip



-- 
If you have a procedure with 10 parameters, you probably missed some. (Kraulis)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53


Received on Fri May 11 2001 - 18:07:39 CEST












Original text of this message