ORDER BY clause in Oracle

ORACLE ORDER BY
Oracle ORDER BY clause is used with the Oracle SELECT statement, however, it does not have a mandatory existence but still is important enough, as it is used to sort or re-arrange the records in the result set.

Syntax:

SELECT expressions
FROM table_name
WHERE conditions;
ORDER BY expression [ ASC | DESC ];


Parameters:
expressions: It is used to specify the columns or calculations to be retrieved.
table_name: It is used to specify the name of the table from which you want to retrieve the records.
conditions: It is used to specify the conditions to be strictly followed for selection.
ASC: It is used to specify the sorting order to sort records in ascending order, but is an optional parameter.
DESC: It is used to specify the sorting order to sort records in descending order, and is also an optional parameter.

Example: Selecting specific fields from a table in default order.
Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
3	        Happy	        11


Query:

SELECT name, age FROM students WHERE age > 10 ORDER BY name;

Output:

ID	NAME	AGE
3	Happy	11
2	Smiley	13

2 rows returned in 0.01 seconds


Explanation:
The ‘students’ is an already existing table. Here we are selecting the specific fields ‘name’ and ‘age’ from the ‘students’ table with a condition that the selected row must have an age greater than 10. The ORDER BY clause defines the order in which the data needs to be displayed after retrieval. Here we are asking to order it by name. The sorting order is always ascending by default.

Example: Selecting specific fields from a table in ascending order.
Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
3	        Happy	        11


Query:

SELECT name, age FROM students WHERE age > 10 ORDER BY id ASC;

Output:

ID	NAME	AGE
2	Smiley	13
3	Happy	11
2 rows returned in 0.01 seconds


Explanation:
The ‘students’ is an already existing table. Here we are selecting the specific fields ‘name’ and ‘age’ from the ‘students’ table with a condition that the selected row must have an age greater than 10. The ORDER BY clause defines the order in which the data needs to be displayed after retrieval. Here we are asking to order it by ‘id’ in ascending order.

Example: Selecting specific fields from a table in descending order.
Students Table:

STUDENT_ID	STUDENT_NAME	STUDENT_AGE
1	        Joy	        5
2	        Smiley	        13
3	        Happy	        11


Query:

SELECT name, age FROM students WHERE age > 10 ORDER BY name DESC;

Output:

ID	NAME	AGE
2	Smiley	13
3	Happy	11
2 rows returned in 0.01 seconds


Explanation:
The ‘students’ is an already existing table. Here we are selecting the specific fields ‘name’ and ‘age’ from the ‘students’ table with a condition that the selected row must have an age greater than 10. The ORDER BY clause defines the order in which the data needs to be displayed after retrieval. Here we are asking to order it by name in descending order.