Oracle Magazine - May/June 2012

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.)

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 SELEC T 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 SELEC T list.

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

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.

6 rows selected.

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.

ENSURING THAT YOU ARE SE T 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:

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

set linesize 32000

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.

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.