Re: [SQL] group by and order by

Well, show me the errors you are getting and the version of ORACLE you are using and I will believe you.

SQL> select c1, count(c2) from test2 group by c1;

C1 COUNT(C2)
-- ----------

G           3
K           1
P           1
ap          1
b           1
c           1
e           1

7 rows selected.

SQL> select c1, count(c2) cn from test2 group by c1 order by cn;

C1 CN
-- ----------

K           1
P           1
ap          1
c           1
e           1
b           1
G           3

7 rows selected.

SQL> select c1, count(c2) cn from test2 group by c1 order by count(c2);

C1 CN
-- ----------

K           1
P           1
ap          1
c           1
e           1
b           1
G           3

7 rows selected.

SQL>

• Jusung Yang

manjay_dwivedi <member23070_at_dbforums.com> wrote in message news:<2415582.1043130151_at_dbforums.com>...
> Dear
> if u use order by nb_connect it'll give u error in oracle
> so u should use order by count(ext_log_date)
>
> check it out then reply me.
>
>
>
> Originally posted by Jusung Yang
> > You aggregate and then you sort the results.
> > I don't think it makes sense to order on a field that is beging
> > aggregated.
> > Order on the "results" of that aggregation would make sense. So "order
> > by nb_connect" would work in your case, and it makes sense.
> >
> >
> > - Jusung Yang
> >
> >
> > erwanpianezza_at_my-deja.com (Erwan) wrote in message
> > news:news:...
> > > Hi, how is it possible to order a group by query on a field that
 is
> > > aggregated ?
> > >
> > > I get the famous ORA-00979 error when i run this one :
> > >
> > > select ext_profil_id, ext_code, count(ext_log_date) as
 nb_connect
> > > from ext_log, extranet_pro where ext_log.ext_profil_id=
> > > extranet_pro.id and ext_action_id=1 group by ext_profil_id,
 ext_code
> > > order by ext_log_date
> > >
> > > thanks,
> > >
 erwan in kemper. Received on Tue Jan 21 2003 - 19:29:12 CET