ORDER BY Clause in SQL with examples

Read this Oracle Tutorial Point article to know what is the use of ORDER BY clause in SQL in ORACLE database. The order of rows returned in a query results are undefined. The ORDER BY clause can be used to sort the rows. The ORDER BY clause should be the last clause in the order of all clauses declared in the SELECT statement. An expression or an alias can be prescribed to sort. By default ordering of data is ascending.

• Numbers 1 to 999
• Dates Earliest – latest
• String A-Z; NULLS --> Last

Syntax:

SELECT [*|COLUMN_NAME(separated by comma(,))] FROM TABLE_NAME 

[WHERE CONDITION(S)] 

[ORDER BY {COLUMN,EXPR}[ASC/DESC]];

The default ordering on a column is ascending to modify the default ordering DESC should be used after the column name. Sorting is implemented on column aliases and also be implemented upon multiple columns. The controversy of sorting is broken only when there rises a conflict of consistency upon the data in a column.

Query using ORDER BY clause:

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP ORDER BY HIREDATE;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP ORDER BY HIREDATE DESC;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP WHERE JOB='MANAGER' ORDER BY SAL;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP WHERE SAL>2500 ORDER BY JOB,NAME DESC;

SQL> SELECT NAME,JOB,DNO,HIREDATE FROM EMP ORDER BY DNO,SAL,HIREDATE;

Single and Multiple Row functions:
The single row function can appear in

• SELECT statement
• WHERE clause
• START WITH clause
• CONNECT BY clause

Types of single row functions are,

• NUMBER.
• CHARACTER.
• DATE.
• CONVERSION.

Single Row Function:
They are used to manipulate data items. They accept one or more arguments and returns only one value for each row returned by the query. An argument can be,

• User Supplied Constants.
• Variable Values.
• Column Names.
• Expressions.

Syntax:

Func_name (column/expr [arg1,arg2,.....])

Features of Single Row Functions:

• Acts on every row returned in the QUERY.
• Return only one result per row.
• May return a different type of value data value than the referenced data type.
• May expert one or more than one argument.
• Can be used in SELECT, WHERE and ORDER BY Clause.
• Can be nested.

Multiple Row Functions:
These functions manipulate multiples of rows to give one result per group of rows.