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 CBE10D1B523
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 Sep 2003 08:55:21 +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 61491-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 17 Sep 2003 05:54:51 -0300 (ADT)
Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52])
	by svr1.postgresql.org (Postfix) with SMTP id 23461D1B52A
	for <pgsql-sql@postgresql.org>; Wed, 17 Sep 2003 05:54:49 -0300 (ADT)
Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ;
 Wed Sep 17 10:54:50 2003 +0200
Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id KAA26855; Wed, 17 Sep 2003 10:54:49 +0200 (METDST)
Message-ID: <3F682159.547F4840@rodos.fzk.de>
Date: Wed, 17 Sep 2003 10:54:49 +0200
From: Christoph Haller <ch@rodos.fzk.de>
X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: martinkuria@hotmail.com
Subject: Re: sub query
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char 9C hex) in message
	header 'Received' Received: \234by rodos.fzk.de...            ^
X-Archive-Number: 200309/149
X-Sequence-Number: 15127

>
> > 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