- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
This chapter is from the book
This chapter is from the book
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 FLListing 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
Sorting by Substrings
To sort results by specific parts of a string, use the functions described in “Extracting a Substring with SUBSTRING()” in Chapter 5. For example, this query sorts by the last four characters of phone:
SELECT au_id, phone
FROM authors
ORDER BY
substr(phone, length(phone)-3);
This query works for Oracle, DB2, MySQL, and PostgreSQL. In Microsoft SQL Server, use substring(phone, len(phone)-3, 4). In Microsoft Access, use Mid(phone, len(phone)-3, 4).
Tips
You can sort by columns that aren’t listed in the SELECT clause (Listing 4.15 and Figure 4.15). This technique won’t work for relative column positions.
Listing 4.15. zip doesn’t appear in the list of columns to retrieve. See Figure 4.15 for the result.
SELECT city, state FROM authors ORDER BY zip ASC;
Figure 4.15 Result of Listing 4.15. This result is sorted by ascending zip code. Rows might appear to be in random order if you sort by an undisplayed column, confusing your end user.
city state ------------- ----- New York NY Bronx NY Sarasota FL Boulder CO San Francisco CA San Francisco CA Palo Alto CA
- You can specify column aliases instead of column names in ORDER BY (Listing 4.16 and Figure 4.16). See “Creating Column Aliases with AS” earlier in this chapter.
Listing 4.16. This query uses column aliases in the ORDER BY clause. See Figure 4.16 for the result.
SELECT au_fname AS "First Name", au_lname AS "Last Name", state FROM authors ORDER BY state ASC, "Last Name" ASC, "First Name" ASC;Figure 4.16 Result of Listing 4.16.
First Name Last Name state ---------- ----------- ----- Hallie Hull CA Klee Hull CA Kellsey CA Wendy Heydemark CO Paddy O'Furniture FL Sarah Buchman NY Christian Kells NY - You can specify the same column multiple times in ORDER BY (but that’s silly).
- If the ORDER BY columns don’t identify each row uniquely in the result, rows with duplicate values will be listed in arbitrary order. Although that’s the case in some of my examples (refer to Figures 4.10, 4.12, and 4.13), you should include enough ORDER BY columns to identify rows uniquely, particularly if the result is to be displayed to an end user.
- According to the SQL standard, the ORDER BY clause is part of a CURSOR declaration and not the SELECT statement. Cursors, which are objects defined inside database applications, are beyond the scope of this book. All SQL implementations let you to use ORDER BY in a SELECT statement (because the DBMS builds a cursor invisibly). Standard SQL also lets ORDER BY appear in window functions (not covered in this book).
- To sort based on conditional logic, add a CASE expression to the ORDER BY clause (see “Evaluating Conditional Values with CASE” in Chapter 5). For example, this query sorts by price if type is “history”; otherwise, it sorts by sales:
SELECT title_id, type, price, sales FROM titles ORDER BY CASE WHEN type = 'history' THEN price ELSE sales END; - You can sort by the results of expressions; Chapter 5 describes how to create expressions by using functions and operators (Listing 4.17 and Figure 4.17).
Listing 4.17. This query sorts by an expression. See Figure 4.17 for the result. I’ve created a column alias for the expression because it would be cumbersome to repeat the expression in the ORDER BY clause and because it creates a more meaningful column label in the result.
SELECT title_id, price, sales, price * sales AS "Revenue" FROM titles ORDER BY "Revenue" DESC;Figure 4.17 Result of Listing 4.17. This result lists titles by descending revenue (the product of price and sales).
title_id price sales Revenue -------- ----- ------- ------------ T07 23.95 1500200 35929790.00 T05 6.95 201440 1400008.00 T12 12.99 100001 1299012.99 T03 39.95 25667 1025396.65 T11 7.99 94123 752042.77 T13 29.99 10467 313905.33 T06 19.95 11320 225834.00 T02 19.95 9566 190841.70 T04 12.99 13001 168882.99 T09 13.95 5000 69750.00 T08 10.00 4095 40950.00 T01 21.99 566 12446.34 T10 NULL NULL NULL
- You can intermingle column names, relative column positions, and expressions in ORDER BY.
- You should create indexes for columns that you sort frequently (see Chapter 12).
- The sequence in which unordered rows appear actually is based on the physical order of rows in the DBMS table. You shouldn’t rely on physical order because it changes often, such as when rows are added, updated, or deleted or an index is created.
- Sorting by relative column position is useful in UNION queries; see “Combining Rows with UNION” in Chapter 9.
- DBMSs restrict the columns that can appear in an ORDER BY clause, depending on data type. For example, in Microsoft SQL Server, you can’t sort by ntext, text, and image columns; and in Oracle, you can’t sort by blob, clob, nclob, and bfile columns. Search your DBMS documentation for SELECT or ORDER BY.
In Microsoft Access you can’t use an expression’s column alias in ORDER BY. To run Listing 4.17, either retype the expression in the ORDER BY clause:
ORDER BY price * sales DESC
or use the relative column position:
ORDER BY 4 DESC