Managing & Using MySQL, 2nd Edition

Ordering and Grouping

The results from a SELECT are, by default, indeterminate in the order they will appear. Fortunately, SQL provides some tools for imposing discipline on this seemingly random list: ordering and grouping.

Basic ordering

You can tell a database to order any results you see by a certain column. For example, if you specify that a query should order the results by last_name, then the results will appear alphabetized according to the last_name value. Ordering is handled by the ORDER BY clause:

SELECT last_name, first_name, age
FROM people
ORDER BY last_name, first_name

In this situation, we are ordering by two columns. You can order by any number of columns. You can also use the special ORDER BY RAND( ) clause to return results in a random order.

If you want to see things in reverse order, add the DESC (descending) keyword:

ORDER BY last_name DESC

The DESC keyword applies only to the field that comes directly before it. If you are sorting on multiple fields, only the field directly before DESC is reversed; the others are sorted in ascending order.

Localized sorting

Sorting is actually a complex problem for applications that need to run on computers all over the world. The rules for sorting strings vary from alphabet to alphabet, even when two alphabets use mostly the same symbols. MySQL handles the problem of sorting by making it dependent on the character set used by the MySQL engine. Out of the box, the default character set is ISO-8859-1 (Latin-1). MySQL uses the ...