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 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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.