Home > Articles

  • Print
  • + Share This
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.

  • + Share This
  • 🔖 Save To Your Account