3.6. 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 ORDER BY title, pub_year LIMIT 20;

This addition will limit 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 would replace the LIMIT clause in the SQL statement with this one:

... LIMIT 20, 10;

As you can see, in a two-argument 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 so:

SELECT books.rec_id, title FROM books, authors WHERE author_last = 'Shakespeare'    AND author_id = authors.rec_id GROUP BY title;

The result of this SQL statement is a list of titles by Shakespeare from the database, with the record identification number displayed for the first one found for each title. Incidentally, GROUP BY will return the same data as ORDER BY on the same column.