Simple SELECT statement page 3
Sorting can be accomplished by the columns absent from SELECT column-list. Naturally, these columns should be presented in the output of FROM clause. For example, to deduce the model list of PCs in the order from greatest price to lowest one, you can write
Notice that the price itself does not be returned by the query. Elimination of duplicates produces ambiguous situation that prevents the behaviour. Thus, the query
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
The same reason prevents from unerror working of the following query that uses grouping
Column "PC.price" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
But if you eject ambiguity (i.e. to do sorting by an aggregate-function value for a group), the query will work:
All the query examples (including erroneous ones) will work in MySQL, which eliminates ambiguity by itself. Do you want to know how? Look in MySQL documentation. :-)