Sometimes it is necessary that the rows returned from your query to be in a specific order. For example, I might want scores from high to low or names in alphabetical order. By default, the database will sort data ascending, smallest to largest. Words are sorted alphabetically. NULLs cannot be sorted, they will be listed as found at the bottom of the results.
The Oracle ORDER BY clause must be used if you require that the rows be sorted. The Oracle database, by default, will not sort the results set, nor does it store rows in a table in a specific order.
The only way to insure that the rows are ordered is to use the Oracle ORDER BY clause. Also, sorting is always performed last. It makes no sense to sort an intermediate row set that we will never see.
The ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the ORDER BY clause, the sort order is defaulted to ASC or ascending order.
Some related examples.
1. Ordering data in the SELECT statement ascending.
SQL> SELECT ID FROM Employee ORDER BY Last_Name ASC; ID ---- 05 08 06 07 01 02 04 03 8 rows selected.
2. Query a number type column with order by desc.
SQL> select last_name, salary 2 from employees 3 where salary > 12000 4 order by salary desc 5 / LAST_NAME SALARY ---------------------------------------------------- ---------- Lawson 30000 Kyte 25000 Bliss 24000 Oracle 20000 Viper 20000 Beck 20000 Java 20000 LAST_NAME SALARY -------------------------------------------------- ---------- Wells 20000 8 rows selected.
3. ORDER BY clause with more than one column:by the first column, and within that column, orders by the second.
SQL> SELECT ID FROM Employee 2 ORDER BY Salary, City; ID ---- 01 07 06 05 08 03 02 04 8 rows selected.
4. Specify ascending or descending for each column.
SQL> SELECT ID, First_Name, Last_Name FROM Employee 2 ORDER BY Salary ASC, Description DESC; ID FIRST_NAME LAST_NAME ---- ---------- ---------- 01 Jason Martin 02 Alison Mathews 03 James Smith 08 James Cat 05 Robert Black 06 Linda Green 07 David Larry 04 Celia Rice 8 rows selected.
5. Query specific columns with order by clause.
SQL> select job_id, salary 2 from employees 3 order by job_id; JOB_ID SALARY ------------------------------ ---------- DBA 20000 DBA 20000 MGR 30000 MGR 25000 PROG 20000 PROG 20000 PROG 20000 JOB_ID SALARY ------------------------------ ---------- PROG 24000 8 rows selected.
6. Use aggregate function in order by clause.
SQL> select job_id, avg(salary) avg_salary 2 from employees 3 group by job_id 4 order by avg(salary) desc 5 / JOB_ID AVG_SALARY ------------------------------ ---------- MGR 27500 PROG 21000 DBA 20000 3 rows selected.
7. Order by 3, 2, 1.
SQL> select last_name, email, hire_date 2 from employees 3 order by 3, 2, 1 4 / LAST_NAME EMAIL HIRE_DATE --------------------------- ------------------------------ -- Kyte tkyte@a.com 13-JUN-98 Bliss bliss@g.com 01-JAN-02 Lawson lawson@g.com 01-JAN-02 Wells wells@g.com 01-JAN-02 Beck clbeck@g.com 10-JUN-08 Java java01@g.com 10-JUN-08 Viper sdillon@a .com 10-JUN-08 LAST_NAME EMAIL HIRE_DATE -------------------------- ------------------------------ - Oracle oracle1@g.com 10-JUN-08 8 rows selected.
8. Get order number by using rownum column.
SQL> SELECT ROWNUM, ename
2 FROM emp;
ROWNUM ENAME
---------- ----------
1 SMITH
2 ALLEN
3 WARD
4 JONES
5 MARTIN
6 BLAKE
7 CLARK
8 SCOTT
8 rows selected.
