4.2. The ORDER BY Clause

You often need to see the results of a query in some kind of order, in other words, sorted by the values in one or more columns, either in ascending order or descending order. By default, columns are sorted in ascending order, but for completeness, you can use the ASC keyword. You use the DESC keyword to specify that a column should be sorted in descending order.

SELECT * | {[DISTINCT] column | expression [alias], ...}
  FROM tablename
  [WHERE condition ... ]
  [ORDER BY column [ASC | DESC], column [ASC | DESC], ...];

The Web Intranet group has requested that the list of employees from HR arrive sorted in ascending order. Janice is able to produce this report quickly by adding an ORDER BY to the existing query:

select employee_id || lpad(last_name,40-length(employee_
id),'.')
"Employee Directory" from employees
order by last_name;

Employee Directory

174................................. Abel
166................................. Ande
130............................. Atkinson
105............................... Austin
204................................. Baer
116................................ Baida
167................................ Banda
172................................ Bates
...
155.............................. Tuvault

112................................ Urman
144............................... Vargas
162.............................. Vishney
196................................ Walsh
120................................ Weiss
200............................... Whalen
149.............................. Zlotkey

107 rows selected.

The column or columns to be sorted don't necessarily need to be in the SELECT clause. If there are NULL values in a column to be sorted, they will appear at the end if the sort is ascending, and they will appear first if the sort is descending.

As you might expect, you can combine both ascending and descending sorts in the same ORDER BY clause. The president, King, needs a monthly report that shows the salaries for each department, in ascending order of department number but in descending order for the salary amount. Janice comes up with the following query for King:

select department_id "Dept",
  last_name || ', ' || first_name "Employee",
  salary "Salary" from employees
order by department_id asc, salary desc;

 Dept Employee                           Salary
----- ------------------------------ ----------
   10 Whalen, Jennifer                     4400
   20 Hartstein, Michael                  13000
   20 Fay, Pat                             6000
   30 Raphaely, Den                       11000
   30 Khoo, Alexander                      3100
   30 Baida, Shelli                        2900
   30 Tobias, Sigal                        2800
   30 Himuro, Guy                          2600
   30 Colmenares, Karen                    2500
   40 Mavris, Susan                        6500
...
   90 King, Steven                        24000
   90 Kochhar, Neena                      17000

90 De Haan, Lex                        17000
  100 Greenberg, Nancy                    12000
  100 Faviet, Daniel                       9000
  100 Chen, John                           8200
  100 Urman, Jose Manuel                   7800
  100 Sciarra, Ismael                      7700
  100 Popp, Luis                           6900
  110 Higgins, Shelley                    12000
  110 Gietz, William                       8300
      Grant, Kimberely                     7000

107 rows selected.

Unlike a WHERE clause, an ORDER BY clause can contain a column alias.

The ASC keyword is not required, but it is specified here for clarity. Notice also how an employee with a NULL department number will end up at the bottom of the list in an ascending sort.