X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id B0C92D1B4EF for ; Wed, 17 Sep 2003 09:57:55 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 71412-09 for ; Wed, 17 Sep 2003 06:57:27 -0300 (ADT) Received: from hotmail.com (sea2-f63.sea2.hotmail.com [207.68.165.63]) by svr1.postgresql.org (Postfix) with ESMTP id 846E4D1B500 for ; Wed, 17 Sep 2003 06:57:24 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 17 Sep 2003 02:57:26 -0700 Received: from 80.205.253.69 by sea2fd.sea2.hotmail.msn.com with HTTP; Wed, 17 Sep 2003 09:57:26 GMT X-Originating-IP: [80.205.253.69] X-Originating-Email: [martinkuria@hotmail.com] From: "Martin Kuria" To: ch@rodos.fzk.de, pgsql-sql@postgresql.org Subject: Re: sub query Date: Wed, 17 Sep 2003 12:57:26 +0300 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 17 Sep 2003 09:57:26.0310 (UTC) FILETIME=[19395460:01C37D02] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/151 X-Sequence-Number: 15129 Thanks Haller, the second one worked thanks a million be blessed Regards +-----------------------------------------------------+ | Martin W. Kuria (Mr.) martin.kuria@unon.org +----------------------------------------------------+ >From: Christoph Haller >To: pgsql-sql@postgresql.org >CC: martinkuria@hotmail.com >Subject: Re: [SQL] sub query >Date: Wed, 17 Sep 2003 10:54:49 +0200 > > > > > > Hi I have this problem, when I try to run this query: > > > > > > SELECT MAX(d), host_position FROM (SELECT host_position, > > > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as >e; > > > > > > am getting and ERROR: Attribute e.host_position must be GROUPed or > > used in > > > an aggregate function. > > > > > > Please to advice what could be the problem and how can I rewrite it >to > > work > > > thanks in advance. > > > > > As the error message says: e.host_position must be GROUPed > > > > so (supposing you want a one row result showing the maximum count) > > > > SELECT MAX(e.d), e.host_position FROM (SELECT host_position, > > COUNT(host_position) as d FROM sss_host GROUP BY host_position) as e > > GROUP BY e.host_position ORDER BY 1 LIMIT 1; > > > > should match your intentions. > > >Just thought about another (less complex) way: > >SELECT COUNT(host_position), host_position FROM >sss_host GROUP BY host_position ORDER BY 1 DESC LIMIT 1; > >Regards, Christoph > > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly _________________________________________________________________ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963