Regards
+-----------------------------------------------------+ | Martin W. Kuria (Mr.) [EMAIL PROTECTED] +----------------------------------------------------+
From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
