SQL Order By Clause

SQL Order By Clause is used in SELECT statement to sort the data either in ascending or descending order, based on one or more columns. Oracle sorts query results in ascending order by default. The basic syntax of ORDER BY clause is as follows:

Syntax

Syntax of Order By Clause

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify using DESC Keyword follow the column name on which you are sorting in the ORDER By Clause.

Key Points

• You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be in column-list.
• You can represent the columns in the ORDER BY clause by specifying the position of a column in the SELECT list, instead of writing the column name, it is known as Positional Sorting.
• You can also use Expressions in the Order By Clause of a SELECT statement using Aliases.
• Sorting can be based on numeric and date values also. Sorting can also be done based on multiple columns.

Example

1. Display Name and Number of all customers in alphabetical order of their Name.

Order By Clause Example

2. List all Salesmen with commission above 10% and result should be in ascending order of  City and reverse order of commission.

Order By Clause Example

3. List all customers in reverse order of city and ratting.

Order By Clause Example

4. List all customers in alphabetical order of city and reverse order of ratting.
(Use Positioning Sort)

Order By Clause Position Sort

Self Exercise

1. Find out the salesmen in alphabetical order whose name begins with ‘G’.
2. Display all orders in reverse order of order date with amount more then Rs. 2000;
3. List all orders with salesman and customer number in the reverse order of salesman and ascending order of customer.