The ORDER BY,select Clause - Oracle DBA

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:

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:

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:

The-ORDER-BY-Clause

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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics