GROUP BY combined with ORDER BY

Question

The GROUP BY clause groups the rows, but it does not necessarily sort the results in any particular order. To change the order, use the ORDER BY clause, which follows the GROUP BY clause. The columns used in the ORDER BY clause must appear in the SELECT list, which is unlike the normal use of ORDER BY. [Oracle by Example, fourth Edition, page 274]

Why is that? Why does using GROUP BY influence the required columns in the SELECT clause?

Also, in the case where I do not use GROUP BY: Why would I want to ORDER BY some columns but then select only a subset of the columns?

Answer 1

Actually the statement is not entirely true as Dave Costa's example shows.

The Oracle documentation says that an expression can be used but the expression must be based on the columns in the selection list.

expr - expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause. Source: Oracle® Database SQL Language Reference 11g Release 2 (11.2) E26088-01 September 2011. Page 19-33

From the the same work page 19-13 and 19-33 (Page 1355 and 1365 in the PDF)

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF01702

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2171079

Answer 2

The bold text from your quote is incorrect (it's probably an oversimplification that is true in many common use cases, but it is not strictly true as a requirement). For instance, this statement executes just fine, although AVG(val) is not in the select list:

WITH DATA AS (SELECT mod(LEVEL,3) grp, LEVEL val FROM dual CONNECT BY LEVEL < 100)
SELECT grp,MIN(val),MAX(val)
FROM DATA
GROUP BY grp
ORDER BY AVG(val)

The expressions in the ORDER BY clause simply have to be possible to evaluate in the context of the GROUP BY. For instance, ORDER BY val would not work in the above example, because the expression val does not have a distinct value for each row produced by the grouping.

As to your second question, you may care about the ordering but not about the value of the ordering expression. Excluding unneeded expressions from the select lists reduces the amount of data that must actually be sent from the server to the client.