From: Steve Crawford <scrawford@pinpointresearch.com>
To: emilu@encs.concordia.ca
Cc: pgsql-sql@postgresql.org
Cc: " <pgsql-general@postgresql.org>
Subject: Re: [SQL] Simple way to get missing number
Date: Tue, 24 Apr 2012 08:42:33 -0700
Message-ID: <4F96C9E9.7000005@pinpointresearch.com> (raw)
In-Reply-To: <4F96B57E.8080306@encs.concordia.ca>
References: <4F96B57E.8080306@encs.concordia.ca>
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: scrawford@pinpointresearch.com, emilu@encs.concordia.ca, pgsql-general@postgresql.org
Subject: Re: [SQL] Simple way to get missing number
In-Reply-To: <4F96C9E9.7000005@pinpointresearch.com>
* 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