Re: tricky query

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 / PRIMARY KEY will create implicit index
> 'id_test_pkey'
> for table 'id_test'
> CREATE TABLE
> cosimo=> insert into id_test values (10); -- and 11, 12, 13, 14
> INSERT 7457570 1
> INSERT 7457571 1
> INSERT 7457572 1
> INSERT 7457573 1
> INSERT 7457574 1
> cosimo=> 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;
> id_new
> --------
> 15
> (1 row)
>
> which if I understand correctly, is the wrong answer to the problem.
> At this point, I'm starting to think I need some sleep... :-)

Correct, in that John's query returns the first empty slot above an
existing filled slot (correct behavior in my case). You could flip
things around a bit to get around thist tho.

Merlin