Oracle ORDER BY Clause

This SQL tutorial focuses on the Oracle ORDER BY clause, and provides explanations, examples and exercises. For this lesson exercises use this link.

The order of records retrieved by a query is undefined, to specify the order in which rows are displayed, you can use the Oracle ORDER BY clause. This tutorial explains how to sort the query result set in an ascending and descending order, and also how to sort by multiple columns.

The ORDER BY clause

Sort the results in ascending and descending order

The Oracle ORDER BY clause is used to sort the query result set, based on a certain column:

ORDER BY column_name

For example, the following Oracle example sorts the result set by price (in ascending order, from the lowest to the highest):

SELECT product_id, product_name, unit_price
FROM products
ORDER BY unit_price

To reverse the order in which rows are displayed, use the keyword DESC after the column name in the Oracle ORDER BY clause:

SELECT product_id, product_name, unit_price
FROM products
ORDER BY unit_price DESC

Sort by multiple columns

You can sort the query result set by more than one column. In the ORDER BY clause, specify the columns and separate them using commas.

This Oracle example sorts the result set by category number and then by price:

SELECT product_name, unit_price 
FROM products
ORDER BY category_id, unit_price

This Oracle example sorts the result set by category number in descending order and then by unit_price:

SELECT product_name , unit_price
FROM products
ORDER BY category_id DESC, unit_price