Server-side sorting in MySQL

When sorting results, MySQL allows for two types of ordering: by group and by item. Below, we look at each in turn in terms of their respective clauses:

• GROUP BY

• ORDER BY

Despite their syntactical similarities, each has its distinct applications and limitations.

GROUP BY

The GROUP BY clause is used to organize results according to the structure of the data returned. The clause itself is appended to the end of the SELECT statement. The basic syntax is:

SELECT <column name(s)> FROM <table name> GROUP BY <column name as key>;

As a rule, GROUP BY cannot be used in conjunction with a universal quantifier ('*') instead of the column name(s). Rather, the column used as the key for sorting must be stated among the column names indicated for the query.

Using the world database, we can ascertain what the database records as the official language of each country in the world, with the following query:

SELECT CountryCode, Language FROM CountryLanguage WHERE IsOfficial...