In General all the rows in the result table have not been ordered in any way. SQL just retrieved the rows in the order in which it found them in the table. Often, however, we need to list the output in a particular order.
This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use Order By clause to impose an order on the query results. The Order By keyword can only be used in Select statements.
The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts query results in ascending order by default.
Syntax: SELECT columns FROM tablename WHERE predicates ORDER BY column ASC/DESC;
SQL ORDER BY clause allows you to:
• Sort data set in single column of values of multiple columns.
• Sort any column in either ascending order (ASC) by default or descending order (DESC).
Refer to sort columns by name, by their position within the output column list of data set, or by using an alias.
To retrieve the Employee NAME, SAL, JOB who do the job of a ‘CLERK’ in Ascending Order according to ENAME.
SQL> SELECT ENAME, SAL, JOB FROM EMP WHERE JOB='CLERK' ORDER BY ENAME ASC;
To retrieve the Employee NAME, SAL, JOB who do the job of a ‘CLERK’ in descending Order according to ENAME.
SQL> SELECT ENAME, SAL, JOB FROM EMP WHERE JOB='CLERK' ORDER BY ENAME DESC;
To retrieve the Employee NAME, JOB, SAL who are working in DEPTNO= 20. Order the record by ENAME Asc and JOB by Desc.
SQL> SELECT ENAME, JOB, SAL FROM EMP WHERE DEPTNO=20 ORDER BY ENAME ASC, JOB DESC;