Re: Number Sequence Query

John,

>  case_num
> ----------
>  020018
>  020020
>  020021
>  02002201
>  020024
>
> I'm only really looking at the numbers that start with 02 and the number
> sequence is the 4 numbers right after the 02.  As you can see by my
> example, 020023 is missing.  I would like to be able to run a query to
> see all the missing numbers in sequence.  I'm not even sure where to start
> looking for information.  I did searches from Google and in the groups
> (which normally answers my questions), but I can't find anything.  Can
> someone help me with this?  Thanks...

A classic SQL problem.   Buy Celko's "SQL for Smarties"; you won't regret it.

Setp 1: Copy the first 6 digits of each number into a temp table and convert
them to INT.  For the rest of the example, we will call that table
"case_numbers" and the colum "cnum".

Step2: Index "cnum" and VACUUM ANALYZE it.

Step 3:  Run this:

SELECT (cbefore.cnum + 1) as begin_gap, (cafter.cnum - 1) as end_gap
FROM (SELECT cnum FROM case_numbers c1
    WHERE NOT EXISTS (select cnum FROM case_numbers c2
        WHERE c2.cnum = c1.cnum +1)
    AND c1.cnum < (SELECT max(cnum) FROM case_numbers)) cbefore,
(SELECT cnum FROM case_numbers c3
    WHERE NOT EXISTS (select cnum FROM case_numbers c4
        WHERE c3.cnum = c4.cnum -1)
    AND c1.cnum > (SELECT min(cnum) FROM case_numbers)) cafter
WHERE NOT EXISTS (SELECT cnum FROM case_numbers c5
    WHERE c5.cnum BETWEEN cbefore.cnum AND cafter.cnum);

(check above for typos!  This is off-the-cuff)

This should give you a list of all gaps in the sequence, in the form of:

begin_gap        end_gap
20023            20023
20037            20041
20079            20079
etc.

What the query does is search for all sequence numbers that do not have a
number immediately following, and then all sequence numbers that do not have
a number immediately preceeding, and matches them up by testing if the gap is
continuous.

As you can imagine with all the sub-selects, it is a RAM-intensive query on
any large data set.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco