Database, SQL and PL/SQL

An Order of Sorts

Part 5 in a series on the basics of the relational database and SQL

By Melanie Caffrey

May/June 2012

Part 4 in this SQL 101 series, “Why WHERE Matters” (Oracle Magazine, March/April 2012), introduced readers to the WHERE clause of a SQL SELECT statement (a query) and the importance of filtering your data. The WHERE clause and the SELECT list tell the database which rows you want your SELECT statement to retrieve. Now that you know how to narrow the scope of the data a query fetches, you’re ready to learn how to sort (or order) the data. This article focuses on the SQL ORDER BY clause and how it behaves in conjunction with certain options and keywords to tell the database how you want retrieved rows to be sorted.

To try out the examples in this and subsequent articles in the series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Express Edition.

If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. SQL_101 is the user account you’ll use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects. When the installation process prompts you to specify schema passwords, enter and confirm passwords for SYS and SYSTEM and make a note of them.

Whether you installed the database software from scratch or have access to an existing Oracle Database instance, download and unzip the SQL script and execute the script to create the example tables for the SQL_101 schema. (View the script in a text editor to get instructions on how to execute the script and information on what it does.)

The SQL queries in this article are executed against tables in the SQL_101 schema with Oracle’s SQL*Plus tool. In addition to discussing the ORDER BY clause, this article provides a closer look at SQL*Plus.

Making Order out of Disarray

Oracle Database table data isn’t stored in any specific order, regardless of the order in which it was inserted into the database. To retrieve rows in either ascending or descending order by column, you must tell the database that you want to do so. For example, you might want to list all employees in the order they were hired, display all employees in order of highest to lowest annual salary, or list the last names of all employees in the accounting department in alphabetical order. You retrieve sorted data by adding an ORDER BY clause to your SELECT statement. ORDER BY is always the last clause in a query.

Listing 1 shows a simple query of the EMPLOYEE table that doesn’t filter or order its result set. Compare Listing 1’s result set with the one in Listing 2. When you use the ORDER BY clause, the result set is in ascending order by default. Listing 2 displays the employees in the EMPLOYEE table sorted by last name in default ascending alphabetical order.

Code Listing 1: Simple query for listing all rows in the EMPLOYEE table

SQL> set linesize 32000
SQL> set feedback on
SQL> select first_name, last_name, hire_date, salary
  2    from employee;
FIRST_NAME            LAST_NAME             HIRE_DATE            SALARY
———————————           —————————————————     ————————————————     ————————
Roger                 Friedli               16-MAY-07             60000
Betsy                 James                 16-MAY-07             60000
Emily                 Eckhardt              07-JUL-04            100000
Frances               Newton                14-SEP-05             75000
Donald                Newton                24-SEP-06             80000
Matthew               Michaels              16-MAY-07             70000
6 rows selected. 

Code Listing 2: Query that lists all rows in ascending alphabetical order by last name

SQL> select first_name, last_name, hire_date, salary
  2    from employee
  3  ORDER BY last_name;
FIRST_NAME            LAST_NAME             HIRE_DATE            SALARY
———————————           —————————————————     ————————————————     ————————
Emily                 Eckhardt              07-JUL-04            100000
Roger                 Friedli               16-MAY-07             60000
Betsy                 James                 16-MAY-07             60000
Matthew               Michaels              16-MAY-07             70000
Frances               Newton                14-SEP-05             75000
Donald                Newton                24-SEP-06             80000
6 rows selected.

You can obtain a result set in descending order by adding the DESC keyword immediately after the column name in the ORDER BY clause. The query in Listing 3 retrieves all employees from the most recent to the least recent date of hire. Note the DESC keyword in the ORDER BY clause. (You can use the ASC keyword to explicitly request ascending order, but it isn’t necessary, because ascending order is the default.)

Code Listing 3: Query that retrieves and displays all employees in descending order by date of hire

SQL> select first_name, last_name, hire_date, salary
  2    from employee
  3  ORDER BY hire_date DESC;
FIRST_NAME            LAST_NAME             HIRE_DATE            SALARY
———————————           —————————————————     ————————————————     ————————
Roger                 Friedli               16-MAY-07             60000
Betsy                 James                 16-MAY-07             60000
Matthew               Michaels              16-MAY-07             70000
Donald                Newton                24-SEP-06             80000
Frances               Newton                14-SEP-05             75000
Emily                 Eckhardt              07-JUL-04            100000
6 rows selected.
Names, Numbers, and Arrangements

Your ORDER BY clause does not need to explicitly name the column(s) by which you want to order the data. If you prefer, you can use the number of the column’s position in the query’s SELECT list. Listing 4 shows a query that retrieves all employees ordered from highest to lowest salary, using the sequence number (4) of the salary column in the query’s SELECT list.

Code Listing 4: Query that retrieves and displays all employees in descending order by column 4

SQL> select first_name, last_name, hire_date, salary
  2    from employee
  3  ORDER BY 4 DESC;
FIRST_NAME            LAST_NAME             HIRE_DATE            SALARY
———————————           —————————————————     ————————————————     ————————
Emily                 Eckhardt              07-JUL-04            100000
Donald                Newton                24-SEP-06             80000
Frances               Newton                14-SEP-05             75000
Matthew               Michaels              16-MAY-07             70000
Betsy                 James                 16-MAY-07             60000
Roger                 Friedli               16-MAY-07             60000
6 rows selected.

A query can sort on multiple columns, using multiple ascension and descension requests. You list the columns (or SELECT list column sequence numbers) in the ORDER BY clause, delimited by commas. The results are ordered by the first column, then the second, and so on for as many columns as the ORDER BY clause includes. If you want any results sorted in descending order, your ORDER BY clause must use the DESC keyword directly after the name or the number of the relevant column.

Listing 5 shows a result set that displays all employees in descending order of hire date (most recent to least recent), within which the employees are further sorted from lowest to highest salary and then by last name. Because ascending order is the default, the second column in Listing 5’s ORDER BY clause doesn’t need to include the ASC keyword; for the same reason, the ASC keyword associated with the last_name column is superfluous.

Code Listing 5: Query that retrieves and displays all employees, using multiple ORDER BY criteria

SQL> select first_name, last_name, hire_date, salary
  2    from employee
  3  ORDER BY hire_date DESC, 4, last_name ASC;
FIRST_NAME            LAST_NAME             HIRE_DATE            SALARY
———————————           —————————————————     ————————————————     ————————
Roger                 Friedli               16-MAY-07             60000
Betsy                 James                 16-MAY-07             60000
Matthew               Michaels              16-MAY-07             70000
Donald                Newton                24-SEP-06             80000
Frances               Newton                14-SEP-05             75000
Emily                 Eckhardt              07-JUL-04            100000
6 rows selected.
Ensuring That You Are Set

Whenever you log in to the database with your username and password, you’re creating a session in the database. You can change certain environment settings for your session that have no effect on other connected sessions (logged-in users). In Listing 1, note that a couple of SQL*Plus set commands appear before the SQL statement. These commands set system variables to customize the SQL*Plus environment settings for the current session. For example, in Listing 1, the following command sets the number of characters that SQL*Plus displays on a line before beginning a new line:

set linesize 32000

The shorter notation for this command is set lines n. This command is helpful if you want to ensure that the lines of your SQL query results do not wrap.

The other set command used in Listing 1 is

set feedback on

This command directs SQL*Plus to display a final count of the number of rows returned in your result set. The shorter notation for this command is set feed on.

The last line displayed in Listing 2’s result set is

6 rows selected.

This line appears because the SQL*Plus feedback setting was turned on (in Listing 1). If you do not want to see this final count of the number of rows returned in your result set, you can turn this setting off with the set feed off command.

Your environment settings will apply to all of your current session’s subsequent query execution results.

Ordering the Unknown

Recall that a null value is one that is not known. Listing 6, for example, lists all employees from the EMPLOYEE table with their manager values. Two of the six returned records have null values in the manager column.

Code Listing 6: Query that displays all employees with their manager values

SQL> select employee_id, first_name, last_name, manager
  2    from employee
  3  ORDER BY manager, last_name;
EMPLOYEE_ID FIRST_NAME           LAST_NAME            MANAGER
——————————— ——————————————       ————————————        ——————————
       6567 Roger                Friedli                   28
       6568 Betsy                James                     28
       7895 Matthew              Michaels                  28
       1234 Donald               Newton                    28
         28 Emily                Eckhardt
         37 Frances              Newton
6 rows selected.

When an ORDER BY clause sorts results in ascending order, any null values are displayed last by default. Conversely, if an ORDER BY clause specifies descending order for a column containing null values, as in Listing 7, the null values are displayed first by default. By using the NULLS FIRST or NULLS LAST option in the ORDER BY clause, you can override the defaults and explicitly specify how you want null values to be sorted. The example in Listing 8 uses the NULLS FIRST option to override the default display-nulls-last behavior of an ORDER BY clause.

Code Listing 7: Query that orders a column containing null values in descending order

SQL> select employee_id, first_name, last_name, manager
  2    from employee
  3  ORDER BY manager DESC, last_name;
EMPLOYEE_ID FIRST_NAME           LAST_NAME            MANAGER
——————————— ——————————————       ————————————        ——————————
         28 Emily                Eckhardt
         37 Frances              Newton
       6567 Roger                Friedli                   28
       6568 Betsy                James                     28
       7895 Matthew              Michaels                  28
       1234 Donald               Newton                    28
6 rows selected.

Code Listing 8: Query that orders a column containing null values with the NULLS FIRST option

SQL> select employee_id, first_name, last_name, manager
  2    from employee
  3  ORDER BY manager NULLS FIRST, last_name;
EMPLOYEE_ID FIRST_NAME           LAST_NAME            MANAGER
——————————— ——————————————       ————————————        ——————————
         28 Emily                Eckhardt
         37 Frances              Newton
       6567 Roger                Friedli                   28
       6568 Betsy                James                     28
       7895 Matthew              Michaels                  28
       1234 Donald               Newton                    28
6 rows selected.
Sorting with Distinction

When including an ORDER BY clause in a SQL SELECT statement, you will usually choose to sort by a column or an expression that’s in the statement’s SELECT list. However, you can also order by columns or expressions that are not in the SELECT list. Listing 9 displays a list of employees ordered by the most recent to the least recent date of hire, within which the employees are sorted alphabetically by last name. Although the sort occurs and displays correctly, only the employees’ first and last names appear in the output, because hire_date is not in the SELECT list.

Code Listing 9: Query that orders by a column not included in the SELECT list

SQL> select first_name, last_name
  2    from employee
  3  ORDER BY hire_date DESC, last_name;
FIRST_NAME                     LAST_NAME
————————————                   —————————————
Roger                          Friedli
Betsy                          James
Matthew                        Michaels
Donald                         Newton
Frances                        Newton
Emily                          Eckhardt
6 rows selected.

If you include the DISTINCT keyword in the SELECT list, only columns or expressions in the SELECT list may be included in the ORDER BY clause. As Listing 10 shows, an error will occur if a query using the DISTINCT keyword tries to order by a column not included in the SELECT list.

Code Listing 10: Query with DISTINCT fails because ORDER BY column is not in the SELECT list

SQL> select DISTINCT hire_date
  2    from employee
  3  ORDER BY manager NULLS FIRST;
ORDER BY manager NULLS FIRST
         *
ERROR at line 3:
ORA-01791: not a SELECTed expression
The Errors of Our Ways

You will inevitably make mistakes while learning to write SQL statements. Being able to interpret the Oracle Database error messages you receive is key to your understanding of SQL. Some error messages make it easy to understand what you’ve done wrong, whereas others are not so straightforward. The best approach is to try to resolve one error message at a time (a process called debugging).

Oracle Database tells you on which line of a query an error has occurred. Listing 10, for example, displays the following error message:

ERROR at line 3:
ORA-01791: not a SELECTed expression

Now you know that the database program is having difficulty with the following line:

3  ORDER BY manager NULLS FIRST;
If you add the MANAGER column to the query’s SELECT list, as the error message implies, you will be able to rerun the statement successfully (assuming that the query contains no other errors).

Syntax errors will probably be the most common errors you make while learning SQL. The importance of carefully reading (and rereading) your statements while debugging cannot be overemphasized. Simple typos, misplaced or missing commas, and unpaired single quotation marks (to name a few common mistakes) can cause a myriad of problems to which the solution might not be readily apparent.

Using Aliases and Format Models

Sometimes you might want your query output to display meaningful headings for specific columns or expressions. You can make this happen by adding a column alias to any of the columns or expressions in your SQL statement’s SELECT list. Listing 11 shows examples of the types of column aliases you can use. Note that if a column alias contains more than one word—or you want it to appear in exact case (uppercase is otherwise the default)—you must enclose the alias in double quotation marks. As Listing 11 shows, you may use a column’s alias in a query’s ORDER BY clause, provided that it is not enclosed in double quotation marks in the SELECT list.

Code Listing 11: Query that uses column aliases

SQL> select first_name first, last_name "Last", hire_date "Start Dt", salary "sal"
  2    from employee
  3  ORDER BY manager NULLS FIRST, first;
FIRST                 Last                  Start Dt                sal
——————————            —————————————————     —————————————        ————————
Emily                 Eckhardt              07-JUL-04            100000
Frances               Newton                14-SEP-05             75000
Betsy                 James                 16-MAY-07             60000
Donald                Newton                24-SEP-06             80000
Matthew               Michaels              16-MAY-07             70000
Roger                 Friedli               16-MAY-07             60000
6 rows selected.

SQL*Plus provides formatting commands that enable you to format attributes for a result set column. For example, Listing 12 illustrates the use of a format model (sometimes referred to as a format mask) applied to the SALARY column. This type of formatting command can be applied to any SELECT list expression that consists of a NUMBER datatype. The shorthand notation for the SQL*Plus COLUMN command is COL.

Code Listing 12: Query that uses a SQL*Plus format model via the COLUMN command

SQL> COLUMN salary FORMAT $999,999
SQL> select first_name, last_name, hire_date, salary
  2    from employee
  3  order by salary desc, last_name;
FIRST_NAME            LAST_NAME             HIRE_DATE             SALARY
———————————           ————————————————      ————————————————    ————————————
Emily                 Eckhardt              07-JUL-04           $100,000
Donald                Newton                24-SEP-06            $80,000
Frances               Newton                14-SEP-05            $75,000
Matthew               Michaels              16-MAY-07            $70,000
Roger                 Friedli               16-MAY-07            $60,000
Betsy                 James                 16-MAY-07            $60,000
6 rows selected. 
SQL Statements in SQL*Plus

SQL*Plus requires the use of a statement terminator, which tells it when to execute your SQL statement. The semicolon (;) is the statement terminator used in most of the examples in this series of articles so far. Alternatively, you can use a forward slash (/) as a statement terminator, provided that it is on a separate line from the rest of the SQL statement. Listing 13 demonstrates the use of both acceptable terminators.

Code Listing 13: Query executed with semicolon and forward slash terminators

SQL> select first_name, last_name, hire_date, salary
  2    from employee
  3  order by hire_date desc, salary desc, last_name;
FIRST_NAME            LAST_NAME             HIRE_DATE             SALARY
———————————           ————————————————      ————————————————    ————————————
Matthew               Michaels              16-MAY-07            $70,000
Roger                 Friedli               16-MAY-07            $60,000
Betsy                 James                 16-MAY-07            $60,000
Donald                Newton                24-SEP-06            $80,000
Frances               Newton                14-SEP-05            $75,000
Emily                 Eckhardt              07-JUL-04           $100,000
6 rows selected.
SQL> select first_name, last_name, hire_date, salary
  2    from employee
  3  order by hire_date desc, salary desc, last_name
  4  /
FIRST_NAME            LAST_NAME             HIRE_DATE             SALARY
———————————           ————————————————      ————————————————    ————————————
Matthew               Michaels              16-MAY-07            $70,000
Roger                 Friedli               16-MAY-07            $60,000
Betsy                 James                 16-MAY-07            $60,000
Donald                Newton                24-SEP-06            $80,000
Frances               Newton                14-SEP-05            $75,000
Emily                 Eckhardt              07-JUL-04           $100,000
6 rows selected.

The SQL*Plus buffer keeps track of the last statement you ran. To re-execute that statement without retyping it, type a forward slash and press Enter. This shortcut is useful, for example, for checking the status of a batch job that is supposed to insert or update records in a particular table.

Only the most recent statement remains in the buffer; it is replaced as soon as you execute another query. To display (or list) the contents of the buffer, you can execute the SQL*Plus LIST command (or just the letter l). For example:

SQL> l
  1  select first_name, last_name, hire_date, salary
  2    from employee
  3* order by hire_date desc, salary desc, last_name
SQL>
Conclusion

This article has shown you how to expand on simple SQL SELECT statements via the ORDER BY clause to order the data you display in a more meaningful way. You’ve seen how the DESC, NULLS FIRST, and NULLS LAST options behave and how null values are handled by default in an ORDER BY clause. You’ve also seen how the presence or the absence of the DISTINCT keyword in a SELECT list affects query execution if the ORDER BY clause includes a column that’s not in the SELECT list.

The next installment in the SQL 101 series will look at character functions.

Next Steps

READ more about relational database design and concepts
 Oracle Database Concepts 11g Release 2 (11.2)
 Oracle Database SQL Language Reference 11g Release 1 (11.1)

 READ SQL 101 Parts 1-4

 DOWNLOAD the script for this article

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.