Re: sub query

From: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Cc: martinkuria(at)hotmail(dot)com
Subject: Re: sub query
Date: 2003-09-17 12:00:56
Message-ID: 3F684CF8.64F0359D@rodos.fzk.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Browse pgsql-sql by date

  From Date Subject
Next Message Kristian Jörg 2003-09-17 12:30:28 Re: Sort order with spaces?
Previous Message Richard Huxton 2003-09-17 11:20:54 Trigger order problems