␡
- 10.1 Introduction
- 10.2 Grouping on One Column
- 10.3 Grouping on Two or More Columns
- 10.4 Grouping on Expressions
- 10.5 Grouping of Null Values
- 10.6 Grouping with Sorting
- 10.7 General Rules for the GROUP BY Clause
- 10.8 The GROUP_CONCAT Function
- 10.9 Complex Examples with GROUP BY
- 10.10 Grouping with WITH ROLLUP
- 10.11 Answers
This chapter is from the book
This chapter is from the book
This chapter is from the book
This chapter is from the book
10.6 Grouping with Sorting
In many cases, a select block containing a GROUP BY clause ends with an ORDER BY clause. And many times the columns specified in that ORDER BY clause are the same as the ones specified in the GROUP BY clause. These statements can be simplified by combining the two clauses.
Example 10.11: For each team, get the number of matches and sort the result in descending order by team number.
The obvious formulation is:
SELECT TEAMNO, COUNT(*) FROM MATCHES GROUP BY TEAMNO ORDER BY TEAMNO DESC
The result is:
TEAMNO COUNT(*)
------ --------
2 5
1 8
Explanation: The specification DESC is a sort direction and indicates that the result must be sorted in a descending order. This statement can be simplified by including the specification DESC in the GROUP BY clause.
SELECT TEAMNO, COUNT(*) FROM MATCHES GROUP BY TEAMNO DESC
If the result must have an ascending sort direction, ASC (ascending) must be specified.