From: | bsimon(at)loxane(dot)com |
---|---|
To: | "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org, pgsql-performance-owner(at)postgresql(dot)org |
Subject: | Réf. : tricky query |
Date: | 2005-06-28 14:50:17 |
Message-ID: | OF6ABFC898.9736EDA7-ONC125702E.00512ADC-C125702E.005178DB@beauchamp.loxane.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
I would suggest something like this, don't know how fast it is ... :
SELECT (ID +1) as result FROM my_table
WHERE (ID+1) NOT IN (SELECT ID FROM my_table) as tmp
ORDER BY result asc limit 1;
"Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
Envoyé par : pgsql-performance-owner(at)postgresql(dot)org
28/06/2005 16:21
Pour : <pgsql-performance(at)postgresql(dot)org>
cc :
Objet : [PERFORM] tricky query
I need a fast way (sql only preferred) to solve the following problem:
I need the smallest integer that is greater than zero that is not in the
column of a table. In other words, if an 'id' column has values
1,2,3,4,6 and 7, I need a query that returns the value of 5.
I've already worked out a query using generate_series (not scalable) and
pl/pgsql. An SQL only solution would be preferred, am I missing
something obvious?
Merlin
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | John A Meinel | 2005-06-28 15:07:50 | Re: tricky query |
Previous Message | Tom Lane | 2005-06-28 14:25:59 | Re: Insert performance vs Table size |