Using ORDER BY Clause in Oracle

This article is written about how to use the Oracle ORDER BY clause with syntax and examples.

Description

The Oracle ORDER BY clause is used to type the documents in your end result set. The ORDER BY clause can only be used in SELECT statements.

Syntax

The syntax for the ORDER BY clause in Oracle/PLSQL is:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions The columns or calculations that you wish to retrieve. tables The tables that you wish to retrieve data from. There need to be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the documents to be selected. ASC Optional. It sorts the end result set in ascending order through expression (default, if no modifier is provider). DESC Optional. It kinds the end result set in descending order with the aid of expression.

Note

If the ASC or DESC modifier is now not supplied in the ORDER BY clause, the outcomes will be sorted via expression in ascending order (which is equivalent to ORDER BY expression ASC.

Example – Sorting without the use of ASC/DESC attribute

The Oracle ORDER BY clause can be used besides specifying the ASC or DESC value. When this attribute is ignored from the ORDER BY clause, the kind order is defaulted to ASC or ascending order.

For example:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city;

This Oracle ORDER BY example would return all files sorted by way of the supplier_city subject in ascending order and would be equivalent to the following ORDER BY clause:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city ASC;

Most programmers pass over the ASC attribute if sorting in ascending order.

Example – Sorting in descending order

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause as follows:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC;

This Oracle ORDER BY example would return all data sorted with the aid of the supplier_city field in descending order.

Example – Sorting by relative position

You can also use the Oracle ORDER BY clause to type with the aid of relative function in the result set, the place the first field in the end result set is 1. The subsequent area is 2, and so on.

For example:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY 1 DESC;

This Oracle ORDER BY would return all records sorted via the supplier_city area in descending order, given that the supplier_city area is in position #1 in the result set and would be equivalent to the following ORDER BY clause:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC;

Example – Using both ASC and DESC attributes

When sorting your end result set the usage of the Oracle ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement.

For example:

SELECT supplier_city, supplier_state
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC, supplier_state ASC;

This Oracle ORDER BY would return all information sorted via the supplier_city field in descending order, with a secondary kind by supplier_state in ascending order.