The ORDER BY Clause


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.

The syntax diagram for SELECT is expanded for ORDER BY as follows:

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

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.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net