As we worked through our example of the Internet Joke Database Website, we had opportunities to explore most aspects of Structured Query Language (SQL). From the basic form of a CREATE TABLE query, to the two syntaxes of INSERT queries, you probably know many of these commands by heart now.
In this chapter, in an effort to tie up loose ends, we'll look at a few more SQL tricks that we haven't seen before, either because they were too advanced, or simply because "it didn't come up". As is typical, most of these will expand on our knowledge of what is already the most complex and potentially confusing SQL command available to us: the SELECT query.
Long lists of information are always easier to use when they're provided in some kind of order. To find a single author in a list from our Authors table, for example, could become an exercise in frustration if we had more than a few dozen registered authors in our database. While at first it might appear that they are sorted in order of database insertion, with the oldest records first and the newest records last, you'll quickly notice that deleting records from the database leaves invisible gaps in this order, which get filled in by newer entries as they're inserted.
What this amounts to is no reliable built-in result sorting capabilities from SELECT queries. Fortunately, there is another optional part of the SELECT query that lets us specify a column by which to sort our table of results. Let's say we wanted to print out a listing of the entries in our Authors table for future reference. If you'll recall, this table has three columns: ID, Name, and EMail. Since ID isn't really interesting in and of itself (it just provides a means to associate entries in this table with entries in the Jokes table), we will usually just list the remaining two columns when we work with this table. Here's a short list of a table of authors:
mysql>SELECT Name, EMail FROM Authors; +-----------------+----------------------+ | Name | EMail | +-----------------+----------------------+ | Joan Smith | jsmith@somewhere.net | | William Shatner | rocketman@earth.net | | Kevin Yank | kevin@sitepoint.com | | Amy Mathieson | amym@hotmail.com | +-----------------+----------------------+
As you can see, the entries are sorted in no particular order. This result is fine for a short list like this, but it would be easier to find a particular author's email address (that of Amy Mathieson, for example) in a very long list of authors, say a few hundred or so, if the authors' names appeared in alphabetical order. Here's how:
mysql>SELECT Name, EMail FROM Authors ORDER BY Name; +-----------------+----------------------+ | Name | EMail | +-----------------+----------------------+ | Amy Mathieson | amym@hotmail.com | | Joan Smith | jsmith@somewhere.net | | Kevin Yank | kevin@sitepoint.com | | William Shatner | rocketman@earth.net | +-----------------+----------------------+
The entries now appear sorted alphabetically by their names. Just as we can add a WHERE clause to a SELECT statement to narrow down the list of results, we can also add an ORDER BY clause to specify a column by which a set of results should be sorted. By adding the keyword DESC after the name of the sort column, you can sort the entries in descending order:
mysql>SELECT Name, EMail FROM Authors ORDER BY Name DESC; +-----------------+----------------------+ | Name | EMail | +-----------------+----------------------+ | William Shatner | rocketman@earth.net | | Kevin Yank | kevin@sitepoint.com | | Joan Smith | jsmith@somewhere.net | | Amy Mathieson | amym@hotmail.com | +-----------------+----------------------+
You can actually use a comma-separated list of several column names in the ORDER BY clause, to have MySQL sort the entries by the first column, then sort any sets of tied entries by the second, and so on. Any of the columns listed in the ORDER BY clause may use the DESC keyword to reverse the sort order.