Re: SQL Query Question

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

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

OK ... I was reckoning that the typical situation this occurs is when you're hunting down some primary keys that failed to be entered, or were deleted by mistake or somesuch.

Joshua> Just glancing at this, all I can do now, ... should one of them be f.n Joshua> minus one instead of plus?

well, f1.n + 1 = f2.n is of course equivalen to f1 = f2.n -1 :-) but that's prolly not what you mean ... Anyway, the thing works as shown, I tested it.

Joshua> I can't work it in my head.

as a matter of fact, neither could I, I just hacked around 'til it worked :-)

Joshua> And, in any case, can this really be any more efficient or elegant or Joshua> anything than generating the full enumeration?

I haven't done any benchmarking, but in my mind there's no question that enumeration will be slower: an enumeration will spend cycles for all the things, both present and missing; my trick (which, incidentally, turned out to already have been published in Celko's SQL for Smarties ... bugger) will only do work for the things that are present. And the join takes O(R+S), R the number of result rows, S the number of the smallest table.

                                                                      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