Re: Number Sequence Query

What you could do is create another table (t_range) that has one column
containing numbers from 020000 to 029999.  Then run the query

select num
from t_range
where num not in (select case_num from t_case_num where case_num like
'num%');

what the above does is that it takes each entry from the t_range table and
checks if it exists in the t_case_num table.  The like clause is used to
take care of cases like "02002201", where there are extra numbers.  If
there are no extra numbers, you could use "where case_num=num".  The issues
with this method is that you have to create an extra table and also it will
give all numbers don't exist in the t_case_num; so if you table entries
ended at 020024, it will give you numbers between 020025 and 029999.
probably not what you want. to fix the latter, you would have recreate the
t_range table with the last value being the largest case_num.  That's
pretty painful...scratch that solution...so do it in programming by writing
a perl script or something.

regards,

Devinder Rajput
Stores Division Corporate Offices
Chicago, IL
(773) 442-6474


                                                                                                                            
                    "John Nix"                                                                                              
                              To:     pgsql-novice@postgresql.org                                       
                    Sent by:                      cc:                                                                       
                    pgsql-novice-owner@post       Subject:     [NOVICE] Number Sequence Query                               
                    gresql.org                                                                                              
                                                                                                                            
                                                                                                                            
                    10/09/2002 04:14 PM                                                                                     
                                                                                                                            
                                                                                                                            





I have a field with case numbers in it that I need to find out what
numbers are left out in the sequence.  The numbers can be formatted fairly
strangely, so let me give you an example:

 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...

John


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster