Re: [SQL] group by and order by

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

That's for Oracle versions before 8.1. In this case, you can't use column aliases in order by clause, but you can use column numbers. For example:

order by 3

will sort by 3rd column in the select clause, i.e. by count(ext_log_date).

Starting with Oracle 8.1 you can use column aliases in order by.

> 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 Wed Jan 22 2003 - 17:17:10 CET