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

First:

The implementation of group by is one which creates a new resultset that differs in structure to the original from clause (table view or some joined tables). That resultset is defined by what is selected.

Not every SQL RDBMS has this restriction, though it is a always requirement that what is ordered by be either an aggregate function of the non-grouped columns (AVG, SUM, etc) or one of the columns grouped by, or functions upon more than one of those results (like adding two columns), because this is a logical requirement of the result of the grouping operation.

Second:

Because you only care about that column for the ordering. For example, you might have a list of the top selling singles without giving their sales (the NYT Bestsellers keeps some details of their data a secret, but do have a ranked list). Of course, you can get around this by just selecting that column and then not using it.

Answer 3

Here my inderstanding

"The GROUP BY clause groups the rows, but it does not necessarily sort the results in any particular order."

-> you can use Group by without order by

"To change the order, use the ORDER BY clause, which follows the GROUP BY clause."

-> the rows are selected by defaut with primary key, and if you add order by you must add after group by

"The columns used in the ORDER BY clause must appear in the SELECT list, which is unlike the normal use of ORDER BY."

Answer 4

Sample table

sample.grades
Name   Grade    Score
Adam   A        95
Bob    A        97
Charlie C       75

First Query using GROUP BY

Select grade, count(Grade) from sample.grades GROUP BY Grade

Output

Grade Count
A     2
C     1

Second Query using order by

select Name, score from sample grades order by score

Output

Bob    A        97
Adam   A        95
Charlie C       75

Third Query using GROUP BY and ordering

Select grade, count(Grade) from sample.grades GROUP BY Grade desc

Output

Grade Count
A     2
C     1

Once you start using things like Count, you must have group by. You can use them together, but they have very different uses, as I hope the examples clearly show.

To try and answer the question, why does group by effect the items in the select section, because that is what group by is meant to do. You can't do the count of a column if you do not group by that column.

Second question, why would you want to order by but not select all the columns? If I want to order by the score, but do not care about the actual grade or even the score I might do

select name from sample.grades order by score

Output

Name
Bob
Adam
Charlie

Answer 5

Which results do you expect to see ordering by columns not listed in the select list and not participated in group by clause? at any case all kind of sort by non-mentioned in SELECT list columns will be omitted so Oracle guys added the restriction correctly.

with c as (
select 1 id, 2 value from dual
union all
select 1 id, 3 value from dual
union all
select 2 id, 3 value from dual
)
select id
from c
group by id
order by count(*) desc

Answer 6

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.

Answer 7

The data is aggregated before it is sorted for the ORDER BY.

If you try to order by any other column (that is not in the group by list or an aggregation function), what value would be used? There is no single value to use for ordering.

I believe that you can use combinations of the values for sorting. So you can say:

order by a+b

If a and b are in the group by. You just cannot introduce columns not mentioned in the SELECT. I believe you can use aggregation functions not mentioned in the SELECT, however.