Обсуждение: sub query
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. Kind regards +-----------------------------------------------------+ | Martin W. Kuria (Mr.) martin.kuria@unon.org +----------------------------------------------------+ _________________________________________________________________ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail
> 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. Regards, Christoph
> > > 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
Thanks Haller, the second one worked thanks a million be blessed Regards +-----------------------------------------------------+ | Martin W. Kuria (Mr.) martin.kuria@unon.org +----------------------------------------------------+ >From: Christoph Haller <ch@rodos.fzk.de> >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
The reason why the first one 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; did not work is because the query needs a ORDER BY 1 DESC LIMIT 1 like the second one. Mind the DESCending order. Regards, Christoph > > Thanks Haller, the second one worked thanks a million be blessed > > > > > > > > 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; > >