Re: Finding available customer numbers

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 06/02/04

  • Next message: mac: "Re: Finding available customer numbers" 
    Date: Wed, 2 Jun 2004 11:11:41 -0500

    This will give you all the gaps :

    SELECT col + 1
      FROM tbl
     WHERE NOT EXISTS( SELECT * FROM tbl t1
                        WHERE t1.col = tbl.col + 1 )
       AND IDNo < ( SELECT MAX( col ) FROM tbl );

    To get the smallest one:

    SELECT MIN( col ) + 1
      FROM tbl
     WHERE NOT EXISTS( SELECT * FROM tbl t1
                        WHERE t1.col = tbl.col + 1 ) ; 

    -- 
Anith
  • Next message: mac: "Re: Finding available customer numbers"