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.
