Table of Contents for

MySQL in a Nutshell, 2nd Edition

For times when we retrieve a long list of data, it can be tidier to sort the data output in a specific order. To do this, we can use the ORDER BY clause. Suppose that we want a list of plays written by William Shakespeare from our database. We could enter the following SQL statement to retrieve such a list and to sort the data by the play title:

SELECT book_id, title, publisher
FROM books
JOIN authors USING(author_id)
JOIN publishers USING(publisher_id)
WHERE author_last = 'Shakespeare'
AND genre = 'play'
ORDER BY title, pub_year;

The ORDER BY clause comes at the end, after the WHERE clause. Here the ORDER BY clause orders the data results first by the title column and then, within title, by the pub_year column, or the year that the particular printing of the play was published. By default, data is sorted in ascending alphanumeric order. If we want to order the results in descending order for the titles, we can just add a DESC flag immediately after the title column in the ORDER BY clause and before the comma that precedes pub_year:

...
ORDER BY title DESC, pub_year;

A large bookstore will have many editions of Shakespeare’s plays, possibly a few different printings for each play. If we want to limit the number of records displayed, we could add a LIMIT clause to the end of the previous SQL statement:

SELECT book_id, title, publisher
FROM books
JOIN authors USING(author_id)
JOIN publishers USING(publisher_id)
WHERE author_last = 'Shakespeare'
AND genre = 'play'
ORDER BY title DESC, pub_year
LIMIT 20;

This addition limits the number of rows displayed to the first 20. The count starts from the first row of the result set after the data has been ordered according to the ORDER BY clause. If we want to retrieve the next 10, we would adjust the LIMIT clause to specify the number of rows to skip, along with the number of records to retrieve. So if we want to skip the first 20 rows and list the next 10 rows from our sort, we replace the LIMIT clause in the SQL statement with this one:

...
LIMIT 20, 10;

As you can see, in a two-argument LIMIT clause, the first argument specifies the number of rows to skip or the point to begin (i.e., 20) and the second argument states the number of rows to display (i.e., 10).

If we want to get just a list of titles by Shakespeare, and we are not concerned with which printing or publisher—that is to say, if we want one row for each title and are satisfied with the first row found for each—we could use the GROUP BY clause like this:

SELECT book_id, title
FROM books
JOIN authors USING(author_id)
WHERE author_last = 'Shakespeare'
GROUP BY title;

The result of this SQL statement is a list of titles by Shakespeare from the database, displaying the record identification number of the first one found for each title.