by Dinesh Thakur Category: SQL Tutorials

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;
ENAME SAL JOB
ADAMS 1100 CLERK
JAMES 950 CLERK
MILLER 1300 CLERK
SMITH 800 CLERK

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;
ENAME SAL JOB
SMITH 800 CLERK
MILLER 1300 CLERK
JAMES 950 CLERK
ADAMS 1100 CLERK

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;
ENAME SAL JOB
ADAMS 1100 CLERK
FORD 3000 ANALYST
JONES 2975 MANAGER
SCOTT 3000 ANALYST
SMITH 800 CLERK
Related SQL




About Dinesh Thakur

Dinesh ThakurDinesh Thakur holds an B.SC (Computer Science), MCSE, MCDBA, CCNA, CCNP, A+, SCJP certifications. Dinesh authors the hugely popular blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps. For any type of query or something that you think is missing, please feel free to Contact us.