Thread: limit with subselect
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.
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
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.kretschmer@schollglas.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Fri, Jul 25, 2008 at 12:02:23PM +0200, A B wrote:
> 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.
You've told us what you're expecting, but not what you're actually
getting so most responses (including this) are going to be guesses! More
information = better responses!
My guess is that you're not getting anything back at all because one of
the "id"s in T2 is null. If that is the case, you need to change the
query to look like:
SELECT id,a,b FROM t WHERE id NOT IN (
SELECT id FROM t2 WHERE c=5 AND id IS NOT NULL)
LIMIT 20;
Sam