Re: [SQL] group by and order by

erwanpianezza_at_my-deja.com (Erwan) wrote in message news:<ef3d78e5.0301200320.4d50428e_at_posting.google.com>...
> 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.

You can order by only on fields which appear or might appear in select clause. Field ext_log_date doens't appear there and can't with current group by clause. Remember, order by is executed after all other clauses of select. Oracle needs field you mention in order by, so it implicitly selects it. More to the point, I can't understand what do you want? How can you order you result on a column which can't appear in aggregated result? Received on Mon Jan 20 2003 - 18:54:22 CET