An Order of Sorts
Part 5 in a series on the basics of the relational database and SQL
By Melanie CaffreyMay/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 DisarrayOracle 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 UnknownRecall 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 ModelsSometimes 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 StepsREAD 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.