Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

Sorting Rows with ORDER BY

Rows in a query result are unordered, so you should view the order in which rows appear as being arbitrary. This situation arises because the relational model posits that row order is irrelevant for table operations. You can use the ORDER BY clause to sort rows by a specified column or columns in ascending (lowest to highest) or descending (highest to lowest) order; see the “Sort Order” sidebar in this section. The ORDER BY clause always is the last clause in a SELECT statement.

To sort by a column:

  • Type:

    SELECT columns
      FROM table
      ORDER BY sort_column [ASC | DESC];

    columns is one or more comma-separated column names, sort_column is the name of the column on which to sort the result, and table is the name of the table that contains columns and sort_column. (sort_column doesn’t have to be in listed in columns.) Specify ASC for an ascending sort or DESC for a descending sort. If no sort direction is specified, ASC is assumed (Listings 4.10 and 4.11, Figures 4.10 and 4.11).

    Listing 4.10. List the authors’ first names, last names, cities, and states, sorted by ascending last name. ORDER BY performs ascending sorts by default, so the ASC keyword is optional. (In practice, ASC typically is omitted.) See Figure 4.10 for the result.

    SELECT au_fname, au_lname, city, state
      FROM authors
      ORDER BY au_lname ASC;

    Figure 4.10 Result of Listing 4.10. This result is sorted in ascending last-name order.

    au_fname  au_lname     city          state
    --------- ------------ ------------- -----
    Sarah     Buchman      Bronx         NY
    Wendy     Heydemark    Boulder       CO
    Hallie    Hull         San Francisco CA
    Klee      Hull         San Francisco CA
    Christian Kells        New York      NY
              Kellsey      Palo Alto     CA
    Paddy     O'Furniture  Sarasota      FL

    Listing 4.11. List the authors’ first names, last names, cities, and states, sorted by descending first name. The DESC keyword is required. See Figure 4.11 for the result.

    SELECT au_fname, au_lname, city, state
      FROM authors
      ORDER BY au_fname DESC;

    Figure 4.11 Result of Listing 4.11. This result is sorted in descending first-name order. The first name of the author Kellsey is an empty string ('') and sorts last (or first in ascending order).

    au_fname  au_lname    city          state
    --------- ----------- ------------- -----
    Wendy     Heydemark   Boulder       CO
    Sarah     Buchman     Bronx         NY
    Paddy     O'Furniture Sarasota      FL
    Klee      Hull        San Francisco CA
    Hallie    Hull        San Francisco CA
    Christian Kells       New York      NY
              Kellsey     Palo Alto     CA

To sort by multiple columns:

  • Type:

    SELECT columns
      FROM table
      ORDER BY sort_column1 [ASC | DESC],
               sort_column2 [ASC | DESC],
               ...
               sort_columnN [ASC | DESC];

    columns is one or more comma-separated column names; sort_column1, sort_column2, ..., sort_columnN are the names of the columns on which to sort the result; and table is the name of the table that contains columns and the sort columns. (The sort columns don’t have to be in listed in columns.) Rows are sorted first by sort_column1; then rows that have equal values in sort_column1 are sorted by the values in sort_column2, and so on. For each sort column, specify ASC for an ascending sort or DESC for a descending sort. If no sort direction is specified, ASC is assumed (Listing 4.12 and Figure 4.12).

Listing 4.12. List the authors’ first names, last names, cities, and states, sorted by descending city within ascending state. See Figure 4.12 for the result.

SELECT au_fname, au_lname, city, state
  FROM authors
  ORDER BY state ASC,
            city  DESC;

Figure 4.12 Result of Listing 4.12.

au_fname  au_lname    city          state
--------- ----------- ------------- -----
Hallie    Hull        San Francisco CA
Klee      Hull        San Francisco CA
          Kellsey     Palo Alto     CA
Wendy     Heydemark   Boulder       CO
Paddy     O'Furniture Sarasota      FL
Christian Kells       New York      NY
Sarah     Buchman     Bronx         NY

SQL lets you specify relative column-position numbers instead of column names in ORDER BY. The position numbers refer to the columns in the result, not the original table. Using column positions saves typing, but the resulting code is unclear and invites mistakes if you reorder the columns in the SELECT clause.

To sort by relative column positions:

  • Type:

    SELECT columns
      FROM table
      ORDER BY sort_num1 [ASC | DESC],
               sort_num2 [ASC | DESC],
               ...
               sort_numN [ASC | DESC];
    columns is one or more comma-separated column names; and sort_num1, sort_num2, ..., sort_numN are integers between 1 and the number of columns in columns, inclusive. Each integer specifies the relative position of a column in columns. table is the name of the table that contains columns. (The sort numbers can’t refer to a column that’s not listed in columns.) The sort order is the same order described in “To sort by multiple columns” earlier in this section (Listing 4.13 and Figure 4.13).

    Listing 4.13. List each author’s first name, last name, city, and state, sorted first by ascending state (column 4 in the SELECT clause) and then by descending last name within each state (column 2). See Figure 4.13 for the result.

    SELECT au_fname, au_lname, city, state
      FROM authors
      ORDER BY 4 ASC, 2 DESC;

    Figure 4.13 Result of Listing 4.13.

    au_fname  au_lname    city          state
    --------- ----------- ------------- -----
              Kellsey     Palo Alto     CA
    Hallie    Hull        San Francisco CA
    Klee      Hull        San Francisco CA
    Wendy     Heydemark   Boulder       CO
    Paddy     O'Furniture Sarasota      FL
    Christian Kells       New York      NY
    Sarah     Buchman     Bronx         NY
  • + Share This
  • 🔖 Save To Your Account