Re: [GENERAL] [SQL] Simple way to get missing number

[Steve Crawford]

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general