fastread homefastrread library fastread menu

Oracle/PLSQL : ORDER BY Clause

Tutorial by:      Date: 2016-04-15 00:31:19

❰ Previous Next ❱

This Oracle tutorial explains how to use the Oracle ORDER BY clause with syntax and examples.

Description

The Oracle ORDER BY clause is used to sort the records in your 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 records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ASC

Optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).

DESC

Optional. It sorts the result set in descending order by expression.

Note

  • If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order (which is equivalent to ORDER BY expression ASC.

Example - Sorting without using ASC/DESC attribute

The Oracle ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the ORDER BY clause, the sort 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 records sorted by the supplier_city field 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 omit 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 records sorted by the supplier_city field in descending order.

Example - Sorting by relative position

You can also use the Oracle ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The next field 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 by the supplier_city field in descending order, since the supplier_city field 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 result set using 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 records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.

 

❰ Previous Next ❱


Oracle/PLSQL

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website