From: Emi Lu <emilu@encs.concordia.ca>
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: pgsql-sql@postgresql.org
Cc: " <pgsql-general@postgresql.org>
Subject: Re: [SQL] Simple way to get missing number
Date: Tue, 24 Apr 2012 14:10:24 -0400
Message-ID: <4F96EC90.6070600@encs.concordia.ca> (raw)
In-Reply-To: <4F96C9E9.7000005@pinpointresearch.com>
References: <4F96B57E.8080306@encs.concordia.ca>
<4F96C9E9.7000005@pinpointresearch.com>
I got it and thank you very much for everyone's help!!
It seems that "left join where is null" is faster comparing with
"except". And my final query is:
select num as missing
from generate_series(5000, #{max_id}) t(num)
left join t1 on (t.num = t1.id)
where t1.id is null;
Emi
On 04/24/2012 11:42 AM, Steve Crawford wrote:
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: emilu@encs.concordia.ca, scrawford@pinpointresearch.com, pgsql-general@postgresql.org
Subject: Re: [SQL] Simple way to get missing number
In-Reply-To: <4F96EC90.6070600@encs.concordia.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox