On 6/28/05, John A Meinel <[EMAIL PROTECTED]> wrote:
> Actually, if you already have a lower bound, then you can change it to:
>
> SELECT t1.id+1 as id_new FROM id_test t1
> WHERE t1.id > id_min
> AND NOT EXISTS
> (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1)
> ORDE
John A Meinel schrieb:
> John A Meinel wrote:
>
>>
>
> Well, I was able to improve it to using appropriate index scans.
> Here is the query:
>
> SELECT t1.id+1 as id_new FROM id_test t1
>WHERE NOT EXISTS
>(SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1)
>ORDER BY t1.id LIMIT 1;
Cosimo wrote:
> I'm very interested in this "tricky query".
> Sorry John, but if I populate the `id_test' relation
> with only 4 tuples with id values (10, 11, 12, 13),
> the result of this query is:
>
>cosimo=> create table id_test (id integer primary key);
>NOTICE: CREATE TABLE / PRIMAR
On Tue, Jun 28, 2005 at 12:02:09 -0400,
Merlin Moncure <[EMAIL PROTECTED]> wrote:
>
> Confirmed. Hats off to you, the above some really wicked querying.
> IIRC I posted the same question several months ago with no response and
> had given up on it. I think your solution (smallest X1 not in X)
> On Tue, Jun 28, 2005 at 12:02:09 -0400,
> Merlin Moncure <[EMAIL PROTECTED]> wrote:
> >
> > Confirmed. Hats off to you, the above some really wicked querying.
> > IIRC I posted the same question several months ago with no response
and
> > had given up on it. I think your solution (smallest X1
John A Meinel wrote:
John A Meinel wrote:
Merlin Moncure wrote:
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.
>> [...]
>
Well, I was able
Merlin Moncure wrote:
On Tue, Jun 28, 2005 at 12:02:09 -0400,
Merlin Moncure <[EMAIL PROTECTED]> wrote:
Confirmed. Hats off to you, the above some really wicked querying.
IIRC I posted the same question several months ago with no response
and
had given up on it. I think your solution
John A Meinel wrote:
>SELECT t1.id+1 as id_new FROM id_test t1
> WHERE NOT EXISTS
> (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1)
> ORDER BY t1.id LIMIT 1;
This works well on sparse data, as it only requires as many index
access as it takes to find the first gap. The simpler "NO
Merlin Moncure wrote:
John Meinel wrote:
See my follow up post, which enables an index scan. On my system with
90k rows, it takes no apparent time.
(0.000ms)
John
=:->
Confirmed. Hats off to you, the above some really wicked querying.
IIRC I posted the same question several months ago wi
Merlin Moncure wrote:
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.
I've already worked out a query using generate_series (not scalable) and
pl/pgsql. An SQL only solution would
> Merlin Moncure wrote:
>
> > 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.
> >
> > I've already worked out a query using generate_series (not scalable)
and
> > pl/pgsql. A
John Meinel wrote:
> See my follow up post, which enables an index scan. On my system with
> 90k rows, it takes no apparent time.
> (0.000ms)
> John
> =:->
Confirmed. Hats off to you, the above some really wicked querying.
IIRC I posted the same question several months ago with no response and
ha
> Not so bad. Try something like this:
>
> SELECT min(id+1) as id_new FROM table
> WHERE (id+1) NOT IN (SELECT id FROM table);
>
> Now, this requires probably a sequential scan, but I'm not sure how
you
> can get around that.
> Maybe if you got trickier and did some ordering and limits. The a
John A Meinel wrote:
Merlin Moncure wrote:
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 retu
Merlin Moncure wrote:
>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?
I would be tempted to join the table to itself like:
SELECT id+1
FROM foo
WHERE id > 0
AND i NOT IN (SELE
Merlin Moncure wrote:
Not so bad. Try something like this:
SELECT min(id+1) as id_new FROM table
WHERE (id+1) NOT IN (SELECT id FROM table);
Now, this requires probably a sequential scan, but I'm not sure how
you
can get around that.
Maybe if you got trickier and did some ordering and
16:21
Pour :
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
Merlin Moncure wrote:
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'
On Tue, Jun 28, 2005 at 10:21:16 -0400,
Merlin Moncure <[EMAIL PROTECTED]> wrote:
> 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 valu
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 q
20 matches
Mail list logo
