Re: limit with subselect

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