MySQL in a Nutshell

Ordering, Limiting, and Grouping

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 books.rec_id, title, publisher
FROM books, authors, publishers
WHERE author_last = 'Shakespeare'
   AND genre = 'play'
   AND author_id = authors.rec_id
   AND publisher_id = publishers.rec_id
ORDER BY title, pub_year;

The ORDER BY clause comes at the end, after the WHERE clause. First, we’re ordering the data results 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.

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 like so:

SELECT books.rec_id, title FROM books, authors, publishers WHERE author_last = 'Shakespeare' AND genre = 'play' AND author_id = authors.rec_id AND publisher_id = publishers.rec_id ...