Re: SQL Query Question

On 11 May 2001 17:07:39 +0100, Philip Lijnzaad <lijnzaad_at_ebi.ac.uk> wrote:
>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.

Well, it won't work for the Yankees, and it may not be more insightful, but I think we'd happily settle for it!

Just glancing at this, all I can do now, ... should one of them be f.n minus one instead of plus? I can't work it in my head. And, in any case, can this really be any more efficient or elegant or anything than generating the full enumeration?

Hope to look at it Monday.

J.

>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 !-)
>--
>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 Sun May 13 2001 - 00:37:38 CEST