When order is important, you can use the ORDER BY clause to request that the server perform a sort on the result set before sending it back to you. This query sorts on the author’s last name:

     select au_lname, au_fname
     from authors
     order by au_lname

     au_lname                                 au_fname
     --------------------------               -----------------
     Bennet                                   Abraham
     Blotchet-Halls                           Reginald
     Carson                                   Cheryl
     DeFrance                                 Michel
     Dull                                     Ann
     Green                                    Marjorie
     Greene                                   Morningstar
     Gringlesby                               Burt
     Hunter                                   Sheryl
     Karsen                                   Livia
     Locksley                                 Charlene
     MacFeather                               Stearns
     McBadden                                 Heather
     O’Leary                                  Michael
     Panteley                                 Sylvia
     Ringer                                   Albert
     Ringer                                   Anne
     Smith                                    Meander
     Straight                                 Dean
     Stringer                                 Dirk
     White                                    Johnson
     Yokomoto                                 Akiko
     del Castillo                             Innes

---

Note:  Why your output doesn’t match mine
The server sort order is specified by the system administrator when the RDBMS is installed for the first time. Possible sort orders include dictionary case insensitive, which sorts alphabetically without regard to upper- or lowercase (apple, Apple, Burger, burger, zebra, Zebra); dictionary case sensitive, which sorts alphabetically and then sorts uppercase letters before lowercase (Apple, apple, Burger, burger, Zebra, zebra); and, most commonly used, binary sort order, which sorts based on the ASCII value of the letters. (There are lots of other search orders, too, but these are the most popular.)

With binary sort order, all the uppercase letters come before lowercase letters. (Apple, Burger, Zebra, apple, burger, zebra). The sort order installed at your site determines what order is used to sort your result sets, and it cannot be overridden. The examples in this book use binary sort order.

---

This query uses the ORDER BY clause to request that the result set be ordered by the au_lname column. Since the binary sort order is installed on the machine used to produce this output, “del Castillo” is sorted at the very end of the list. Lowercase d has an ASCII value of 100, and an uppercase Y has an ASCII value of 89.

Sorting by Multiple Columns

Let’s suppose I want a list of authors, organized by where they live. I could start out by writing a query like this one:

     select au_lname, state
     from authors
     order by state

     au_lname                                 state
     ---------------------------------------- -----
     White                                    CA
     Green                                    CA
     Carson                                   CA
     O’Leary                                  CA
     Straight                                 CA
     Bennet                                   CA
     Dull                                     CA
     Gringlesby                               CA
     Locksley                                 CA
     Yokomoto                                 CA
     Stringer                                 CA
     MacFeather                               CA
     Karsen                                   CA
     Hunter                                   CA
     McBadden                                 CA
     DeFrance                                 IN
     Smith                                    KS
     Panteley                                 MD
     del Castillo                             MI
     Blotchet-Halls                           OR
     Greene                                   TN
     Ringer                                   UT
     Ringer                                   UT

     (23 row(s) affected)

This does the job I want, but there are a lot of California authors. I might want to alphabetize the authors within each state to make an author easier to find. Unordered data is often disorienting and hard to use. To perform this operation, I need to sort by both state and au_lname.

To sort by additional columns, simply specify those additional columns in the ORDER BY list. To sort the authors first by state, as in the last example, and then by name within each state, use this query:

     select au_lname, state
     from authors
     order by state, au_lname

     au_lname                                 state
     ------------------------                 ------
     Bennet                                   CA
     Carson                                   CA
     Dull                                     CA
     Green                                    CA
     Gringlesby                               CA
     Hunter                                   CA
     Karsen                                   CA
     Locksley                                 CA
     MacFeather                               CA
     McBadden                                 CA
     O’Leary                                  CA
     Straight                                 CA
     Stringer                                 CA
     White                                    CA
     Yokomoto                                 CA
     DeFrance                                 IN
     Smith                                    KS
     Panteley                                 MD
     del Castillo                             MI
     Blotchet-Halls                           OR
     Greene                                   TN
     Ringer                                   UT
     Ringer                                   UT

     (23 row(s) affected)

The results are sorted first by state; then when authors live in the same state, they are sorted by last names within the state.