X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6B6EAD1B20E for ; Sun, 13 Jun 2004 00:22:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 46284-09 for ; Sun, 13 Jun 2004 03:21:57 +0000 (GMT) Received: from fep13.012.net.il (fep13.012.net.il [212.117.129.242]) by svr1.postgresql.org (Postfix) with ESMTP id CAB77D1B229 for ; Sun, 13 Jun 2004 00:21:55 -0300 (ADT) Received: from [127.0.0.1] ([80.178.84.246]) by fep13.012.net.il with ESMTP id <20040613032033.MHUS4510.fep13@[80.178.84.246]> for ; Sun, 13 Jun 2004 06:20:33 +0300 Received: from [127.0.0.1] ([127.0.0.1]) by [127.0.0.1] with ESMTP (SpamPal v1.50) sender ; 13 Jun 2004 06:21:17 +0300 Date: Sun, 13 Jun 2004 06:21:17 +0300 From: Vitaly Belman Reply-To: Vitaly Belman X-Priority: 3 (Normal) Message-ID: <2393942843.20040613062117@012.net.il> To: pgsql-performance@postgresql.org Subject: Additional select fields in a GROUP BY MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=PRIORITY_NO_NAME X-Spam-Level: X-Archive-Number: 200406/141 X-Sequence-Number: 7199 Hello, Consider the following query: select t1field1, avg(t2fieild2) from t1, t2 where t1.field1 = t2.field2 group by t1field1 That works fine. But I'd really like to see more fields of t1 in this query, however I can't add them into the select because they're not part of the GROUP BY, thus I have to add them to there too: select t1field1, t1field2, t1field3, avg(t2fieild2) from t1, t2 where t1.field1 = t2.field2 group by t1field1, t1field2, t1field3 The problem is that addind them all to GROUP BY causes a performance loss.. The only solution I found is using a subquery like this: select * from t1, (select t1field1, avg(t2fieild2) from t1, t2 where t1.field1 = t2.field2 group by t1field1) t1inner where t1.field1 = t1inner.field1 It works just fine.. But I prefer not to use subqueries unless I am really forced to due to the design of my application. Another solution I considered is using aggreate function like that: select t1field1, max(t1field2), max(t1field3), avg(t2fieild2) from t1, t2 where t1.field1 = t2.field2 group by t1field1 Sadly, this caused the same performance... I wonder though, is it possible to make an aggregate function like first(), last() in Oracle (IIRC)? I believe that in such cases MySQL does first() by itself. Other ideas are welcome too. Regards, Vitaly Belman ICQ: 1912453 AIM: VitalyB1984 MSN: tmdagent@hotmail.com Yahoo!: VitalyBe