Questions and Answers

"It is better to know some of the questions than all of the answers."
James Thurber (1894 - 1961)

"Natural selection is not evolution."
Ronald Fisher

Off-Topic

In our musicarchive examples, you may have noticed a band called The Indelicates. First of all, the make great music and besides of this they are a good example of the Pay-What-You-Want system, which is an alternative to our oligopolistic music industry. Read the Interview With Simon Indelicate of the Indelicates.

SQL Courses

Are you looking for an SQL beginners course? Bodenseo has most probably the right one for you: MySQL for Beginners

Ordering the Output with ORDER BY

Usually, the rows of a result set of a SELECT query are not in any particular order. If you want to have the result ordered, you will have to use the ""ORDER BY" clause.
The following SELECT command returns an ordered output of our cd collection from the previous chapter. The output is ordered by artist and then by title:
mysql> SELECT * FROM cd ORDER BY artist, title;
+-----------+----------------------+--------------------------------+-------------+------+
| cd_number | artist               | title                          | genre       | year |
+-----------+----------------------+--------------------------------+-------------+------+
|        27 | Igor Stravinsky      | Le Sacre du Printemps; The Fir | Classical   | 2003 |
|        28 | Igor Stravinsky      | Works for Violin and Piano     | Classical   | 1987 |
|        22 | John Mayall          | A Hard Road                    | Blues       | 1967 |
|        21 | John Mayall          | Blues Breakers with Eric Clapt | Blues       | 1966 |
|        25 | John Mayall          | In the Palace of the King      | Blues       | 2007 |
|        24 | John Mayall          | Jazz Blues Fusion              | Jazz Blues  | 1971 |
|        20 | John Mayall          | John Mayall Plays John Mayall  | Blues       | 1964 |
|        23 | John Mayall          | The Turning Point              | Blues       | 1967 |
|        26 | John Mayall          | Tough                          | Blues Rock  | 2009 |
|        10 | Led Zeppelin         | Houses of The Holy             | Rock        | 1971 |
|         6 | Led Zeppelin         | Led Zeppelin                   | Rock        | 1969 |
|         7 | Led Zeppelin         | Led Zeppelin II                | Rock        | 1969 |
|         8 | Led Zeppelin         | Led Zeppelin III               | Rock        | 1970 |
|         9 | Led Zeppelin         | Led Zeppelin IV                | Rock        | 1971 |
|        29 | Ludwig van Beethoven | The Five Piano Concertos       | Classical   | 1995 |
|         4 | Massive Attack       | 100th Window                   | Trip Hop    | 2003 |
|         1 | Massive Attack       | Blue Lines                     | Trip Hop    | 1991 |
|         5 | Massive Attack       | Heligoland                     | Trip Hop    | 2010 |
|         3 | Massive Attack       | Mezzanine                      | Trip Hop    | 1998 |
|         2 | Massive Attack       | Protection                     | Trip Hop    | 1994 |
|        18 | Miles Davis          | Amandla                        | Jazz        | 1989 |
|        16 | Miles Davis          | Bitches Brew                   | Jazz        | 1970 |
|        19 | Miles Davis          | Doo-Bop                        | Jazz        | 1992 |
|        14 | Miles Davis          | Kind of Blue                   | Jazz        | 1959 |
|        15 | Miles Davis          | Sketches of Spain              | Jazz        | 1960 |
|        17 | Miles Davis          | Tutu                           | Jazz        | 1986 |
|        13 | Nils Petter Molvaer  | Barboon Moon                   | Fusion Jazz | 2011 |
|        11 | Nils Petter Molvaer  | Khmer                          | Fusion Jazz | 1997 |
|        12 | Nils Petter Molvaer  | Solid Ether                    | Fusion Jazz | 2000 |
|        30 | The Indelicates      | American Demo                  | Indie Rock  | 2008 |
|        31 | The Indelicates      | American Demo                  | Indie Rock  | 2010 |
+-----------+----------------------+--------------------------------+-------------+------+
31 rows in set (0.00 sec)
You can see, that the results are ordered in ascending alphabetical order. This is the default. We could have written the previous command with the additional keyword ASC, thus making clear that we want the output in ascending order. This is superfluous in MySQL and in Oracle, where ascending is the default, but to be compatible with other SQL databases is might be a good idea to use the ASC always:
SELECT * FROM employee ORDER BY lname ASC;
If you use DESC instead of ASC, the rows will appear in descending alphabetical order:
SELECT * FROM employee ORDER BY lname DESC;

Sorting with Numeric Placeholders

Instead of using the column names, you can use the position of the columns after the SELECT keyword. This is especially usefull, if you use literals, expressions or functions in the SELECT clause. We demonstrate this in the following example:
mysql> SELECT UPPER(artist) Artist, title, 2012 - year "Age in Years", "new" FROM cd WHERE year > 2000 ORDER BY 3,1;
+---------------------+--------------------------------+--------------+-----+
| Artist              | title                          | Age in Years | new |
+---------------------+--------------------------------+--------------+-----+
| NILS PETTER MOLVAER | Barboon Moon                   |            1 | new |
| MASSIVE ATTACK      | Heligoland                     |            2 | new |
| THE INDELICATES     | American Demo                  |            2 | new |
| JOHN MAYALL         | Tough                          |            3 | new |
| THE INDELICATES     | American Demo                  |            4 | new |
| JOHN MAYALL         | In the Palace of the King      |            5 | new |
| IGOR STRAVINSKY     | Le Sacre du Printemps; The Fir |            9 | new |
| MASSIVE ATTACK      | 100th Window                   |            9 | new |
+---------------------+--------------------------------+--------------+-----+
8 rows in set (0.00 sec)

mysql> 
The output is ordered in numerical ascending order by the age of the CD and if the age is the same, the ordering ascends on the name of the artist.