Re: [SQL] Simple way to get missing number

[Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>]

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

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general