From: | Mauricio Carvalho de Oliveira <carvalho(at)dt(dot)fee(dot)unicamp(dot)br> |
---|---|
To: | pgsql-sql(at)hub(dot)org |
Subject: | Problem with HAVING clause |
Date: | 1999-02-22 19:49:26 |
Message-ID: | 36D1B4C6.9F388890@dt.fee.unicamp.br |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have created the following two tables:
CREATE TABLE tab1 (Id INTEGER, Name TEXT);
CREATE TABLE tab2 (Id INTEGER, Attribute INTEGER);
populated as:
INSERT INTO tab1 VALUES (0, 'name 1');
INSERT INTO tab1 VALUES (1, 'name 2');
INSERT INTO tab1 VALUES (2, 'name 3');
INSERT INTO tab2 VALUES (0, 0);
INSERT INTO tab2 VALUES (0, 1);
INSERT INTO tab2 VALUES (0, 4);
INSERT INTO tab2 VALUES (0, 5);
INSERT INTO tab2 VALUES (1, 0);
INSERT INTO tab2 VALUES (2, 0);
INSERT INTO tab2 VALUES (2, 1);
test=> SELECT * FROM tab1;
id|name
--+------
0|name 1
1|name 2
2|name 3
(3 rows)
test=> SELECT * FROM tab2;
id|attribute
--+---------
0| 0
0| 1
0| 4
0| 5
1| 0
2| 0
2| 1
(7 rows)
and I would like to list the 'names' which are associated to more than 'N'
attributes. For 'N=2' I can get the 'id's by
test=> SELECT Id FROM tab2 GROUP BY Id HAVING COUNT(*) > 2;
id
--
0
(1 row)
so that I expected to be able to use this information in a subquery like:
test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2
GROUP BY tab2.Id HAVING COUNT(*) > 2);
ERROR: rewrite: aggregate column of view must be at rigth side in qual
which gives me nothing but the strange error above. Although I do not completely
understand this message I have rewritten the query so that the aggregate does
appear in the right side of the HAVING clause as:
test=> SELECT tab1.Name FROM tab1 WHERE tab1.Id IN (SELECT tab2.Id FROM tab2
GROUP BY tab2.Id HAVING 2 < COUNT(*));
name
------
name 1
name 2
name 3
(3 rows)
but now what I have is something I really do not want: all names! Notice that the
"inverted" subquery still gives the correct result:
test=> SELECT tab2.Id FROM tab2 GROUP BY tab2.Id HAVING 2 < COUNT(*);
id
--
0
(1 row)
Is this a bug or did I miss some point? Does anybody have some ideas?
By now I do the job with the join:
test=> SELECT tab1.Name FROM tab1, tab2 WHERE tab1.Id=tab2.Id GROUP BY tab1.Name
HAVING COUNT(*) > 2;
name
------
name 1
(1 row)
I believe this is less efficient than the above since I have to 'join' two tables
before applying the HAVING clause. Any comments?
TIA
P.S.: I am using PostgreSQL v. 6.4.2
--
Mauricio C. de Oliveira
mailto:carvalho(at)dt(dot)fee(dot)unicamp(dot)br
http://www.dt.fee.unicamp.br/~carvalho
From | Date | Subject | |
---|---|---|---|
Next Message | Mauricio Carvalho de Oliveira | 1999-02-22 19:53:29 | Problem with HAVING clause |
Previous Message | Bruce Momjian | 1999-02-22 19:27:16 | Re: [SQL] questions on features |