Sometimes it is necessary that the rows returned from your query to be in a specific order. The order of rows returned in the result set is undefined and may change each time the query is executed. Oracle database provides ORDER BY clause that can be used to sort the rows in the required order.

• ORDER BY clause is only used with SELECT statement.
• ORDER BY clause should be the last clause in a SELECT statement.
• ORDER BY clause can take ASC OR DESC keywords to sort the data either in ascending or descending order. Default order for columns specified in a ORDER BY clause is ascending (ASC).

Default ascending ordering in Oracle is defined as below:

• • Numbers -> 0 - 9
  • Dates -> Earliest - Latest
  • Strings -> Follows ASCII values
  • NULL -> Last

• An expression or an alias can be specified to ORDER BY clause for sorting. So, ORDER BY clause is the only clause that can operate on column aliases. Any other Oracle function or clause can’t operate on column aliases.

Q : Get the employee names, department number, salary and designation for all employees sorted in chronological order of their names?

Ans:

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Ename ASC;

If you do not specify sort order, default is ascending (ASC). So, below query produces same output as above.

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Ename;

Q : Get the employee names, department number, salary and designation for all employees sorted in descending order of their salary?

Ans:

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Sal DESC;

Q : Get the employee names, department number, salary and designation for all employees sorted by their department in ascending order?

Ans:

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Deptno;

The ORDER BY clause allows you to sort data by multiple columns where each column may have different sort orders. This is required because, the result set is sorted based on the first column. If any tie happens, Oracle ORDER BY looks for the next column for sorting. If no column is found, the sorting becomes undefined within the group of tie rows.

Q : Get the employee names, department number, salary and designation for all employees. Employees must be sorted by their department and then in chronological order of their names?

Ans:

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Deptno, Ename DESC;

Columns in the ORDER BY must appear in the SELECT list.

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Deptno ASC, Job DESC, Sal DESC, Ename;

--Error when column doesn't appear in the selection

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Deptno ASC, Job DESC, Empno;

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY Deptno ASC, Job DESC, Sal DESC, Ename;

You can specify sort columns or SELECT list column sequence numbers or combination of both (column names and column sequence number) in the ORDER BY clause, delimited by commas.

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY 1;

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY 3 DESC, 1;

SELECT Ename, Deptno, Sal, Job
  FROM Emp
 ORDER BY 4, Deptno, 3 DESC;

SELECT Ename, Deptno, Sal*12, Job
  FROM Emp
 ORDER BY 4, Deptno, Sal*12 DESC;

Q : Can you specify WHERE clause with ORDER BY?

Ans: Yes, ORDER BY is the last clause in a SELECT statement.

Q : Get the details of all managers in descending order of their salary?

Ans:

SELECT *
  FROM Emp
 WHERE Job = 'MANAGER'
 ORDER BY Sal DESC;

ORDER BY is the only clause that can take column alias.

--Combination of column alias, column names and column sequence number

SELECT 
      Ename  "Employee Name", 
      Deptno "Department", 
      Sal    Salary, 
      Job
  FROM Emp
 ORDER BY "Department", Job, Salary DESC, 1;