Introducing SELECT
Pages: 1, 2
Sorting data with SELECT
SQL is designed to manipulate data, so you can safely assume that SQL has built-in tools for doing almost any sort of common data manipulation task. One exceedingly simple example is sorting data alphabetically. We can take any of the SELECT queries from the previous section and sort them in alphabetical order using the ORDER BY clause.
SELECT Artist, Title FROM MusicCollection ORDER BY Title;
| Artist | Title |
| -------------------- | -------------------- |
| Lyle Lovett | Joshua Judges Ruth |
| The Beach Boys | Pet Sounds |
| Peter Gabriel | Security |
| Bruce Hornsby | The Way it Is |
Now we've got Title-sorted data as a result of the SELECT statement. There are plenty of variations on this theme! One possibility is that we can sort in reverse order
SELECT Artist, Title FROM MusicCollection ORDER BY Title DESC;
| Artist | Title |
| -------------------- | -------------------- |
| Bruce Hornsby | The Way it Is |
| Peter Gabriel | Security |
| The Beach Boys | Pet Sounds |
| Lyle Lovett | Joshua Judges Ruth |
The DESC keyword is short for DESCending order. The ASCending keyword can also be used, but since it is the default behavior, it is almost always left out.
You can sort on several different columns, each in its own order:
SELECT Artist, Title FROM MusicCollection ORDER BY Title DESC, Artist ASC;
| Artist | Title |
| -------------------- | -------------------- |
| Bruce Hornsby | The Way it Is |
| Peter Gabriel | Security |
| The Beach Boys | Pet Sounds |
| Lyle Lovett | Joshua Judges Ruth |
In this example, Title is ordered in descending alphabetical order and is then further sorted by Artist in ascending order (which has no effect in this case since all the artists and titles are distinct). We can even sort by columns that aren't displayed:
SELECT Artist, Title FROM MusicCollection ORDER BY Year;
| Artist | Title |
| -------------------- | -------------------- |
| Pet Sounds | The Beach Boys |
| The Way it Is | Bruce Hornsby |
| Security | Peter Gabriel |
| Joshua Judges Ruth | Lyle Lovett |
This selection is properly sorted by Year, even though it wasn't displayed in the output of the SELECT statement.
Next steps
We've only just started getting your feet wet with data selection. So far, the syntax is
SELECT column_name(s) [AS alias] FROM table_name [ORDER BY column_name(s) (ASC | DESC) ]
where the statements in square brackets are optional.
Next, we're going to start filtering the results of the SELECT command using the WHERE clause. This is one of the most powerful tools/capabilities you'll run into as you learn aboutSQL.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Discuss this article in the O'Reilly Network Linux Forum.
Return to the Linux DevCenter.
