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