Oracle ORDER BY clause

➔ The ORDER BY clause is an optional clause in the SELECT statement.

➔ Oracle stores data in tables in no particular order, regardless of the order in which rows are inserted into the tables.

➔ Data can be collected randomly from different sources and stored in an Oracle database without maintaining any specific order.

➔ To query rows in ascending or descending order by a column, Oracle must be explicitly instructed that it needs to do so.

➔ This optional ORDER BY clause goes at the end of SELECT statement

Basic ORDER BY syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC];

Example: Sort employees by their salary from highest to lowest

SELECT employee_id, first_name, salary 
FROM hr.employees 
ORDER BY salary DESC;

Example: First, sort the results within each department by department_id (ascending), then by salary (descending).

SELECT first_name, department_id, salary 
FROM hr.employees 
ORDER BY department_id ASC, salary DESC;

Example: Instead of the column name, we can use the numeric position of the column in the SELECT list (1 for the first column, 2 for the second, etc).

SELECT first_name, hire_date, salary 
FROM hr.employees 
ORDER BY 2 DESC; 

Example: We can explicitly place a NULL value at the beginning (FIRST) or at the end (LAST) of the list.

SELECT first_name, commission_pct, salary 
FROM hr.employees 
ORDER BY commission_pct ASC NULLS FIRST;

Example: It is possible to sort query data sets based on calculated values ​​or numeric expressions.

SELECT first_name, salary, salary*10/100 AS bonus 
FROM hr.employees 
ORDER BY salary + bonus;