sql - Oracle: NOT A GROUP BY EXPRESSION ERROR -

below query. not sure cause not group expression error. appreciated. =)

select c.courseid, c.coursename, avg(a.mark) average_mark course c, assessment c.courseid = a.courseid group c.courseid, c.coursename order mark desc; 

try:

select c.courseid,        c.coursename,        avg(a.mark) average_mark   course c        inner join assessment                on c.courseid = a.courseid group  c.courseid,           c.coursename order  3 desc; -- or order average_mark desc 

as aggregate several values of mark in order compute average, becomes impossible sort on each value of mark. have sort on result of computation, i.e. average_mark.

---

from more general point of view, allowed order by non selected column if column part of queried tables , if don't use group by or distinct (unless group by non displayed column, can order by it).

the reason simple: if use group by or distinct, several rows potentially displayed one. non displayed values in "merged" rows can potentially different each other, making order by impossible on values.

some dbms (mysql @ least) behave differently, allowing ordering by non displayed values, group by. mysql seems order first encountered value of non displayed value (see fiddle). so, better keep in mind should avoided, prevent unpredictible results.

edit: see documentation mysql group by handling.