Re: limit with subselect

Yes it obviously does! I must admit that (as usual) I found the error
shortly after sending the post.
The problem was that instead of

select id,a,b from T where id not in (select id from T2 where c=5)

I wrote

select id,a,b from T where id not in (select YYY from T2 where c=5)

where YYY was a non existant column in T2.
I think that I sometimes got an empty set from the subquery, and sometimes not.

2008/7/25 A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com>:
> am Fri, dem 25.07.2008, um 12:02:23 +0200 mailte A B folgendes:
>> Sorry if this is a double posting, I think the previous message was lost.
>>
>> I have two tables
>> T (
>> id int primary key,
>> a int,
>> b int
>> )
>>
>> T2 (
>> id int references T,
>> c int
>> );
>>
>> and I wish to get 20 lines from T like this
>>
>> select id,a,b from T where id not in (select id from T2 where c=5) limit 20;
>>
>> but that does not seem to work. How can I get what I want? What 20
>> records are selected is not important. I just need 20.
>
> Works for me:
>
> test=# create table t (id serial primary key, a int);
> NOTICE: CREATE TABLE will create implicit sequence "t_id_seq" for serial column "t.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
> CREATE TABLE
> test=*# create table t2 (id int references t, c int);
> CREATE TABLE
> test=*# insert into t (a) select * from generate_Series(10,50) s;
> INSERT 0 41
> test=*# insert into t2 values (1,1);
> INSERT 0 1
> test=*# insert into t2 values (2,2);
> INSERT 0 1
> test=*# insert into t2 values (3,3);
> INSERT 0 1
> test=*# insert into t2 values (4,4);
> INSERT 0 1
> test=*# select * from t where id not in (select id from t2 where c=3) limit 5;
> id | a
> ----+----
> 1 | 10
> 2 | 11
> 4 | 13
> 5 | 14
> 6 | 15
> (5 rows)
>
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>