Re: [SQL] Simple way to get missing number

On 04/24/2012 07:15 AM, Emi Lu wrote:
> Good morning,
>
> May I know is there a simple sql command which could return missing
> numbers please?
>
> For example,
>
> t1(id integer)
>
> values= 1, 2, 3 .... 5000000
>
> select miss_num(id)
> from   t1 ;
>
>
> Will return:
> ===============
> 37, 800, 8001
>
> T

select generate_series(1,5000000) except select id from t1;

Example

select anumber from fooo;
  anumber
---------
        1
        3
        5
        7
        9
       11
       13
       15

select generate_series(1,15) except select anumber from fooo order by 1;
  generate_series
-----------------
                2
                4
                6
                8
               10
               12
               14

Cheers,
Steve