Sort Data: ORDER BY

To explicitly sort data retrieved using a SELECT statement, the ORDER BY clause is used. ORDER BY takes the name of one or more columns by which to sort the output.

Sorting by Single Column

SELECT prod_name
FROM Products
ORDER BY prod_name;

Caution: Position of ORDER BY Clause

When specifying an ORDER BY clause, be sure that it is the last clause in your SELECT statement. If it is not the last clause, an error will be generated. The only one exception is LIMIT. If you use LIMIT/OFFSET, it has to follow ORDER BY.

Tip: Sorting by Non-selected Columns

Although more often than not the columns used in an ORDER BY clause will be ones selected for display, this is actually not required. It is perfectly legal to sort data by a column that is not retrieved.

Sorting by Multiple Columns

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;

To sort by multiple columns, simply specify the column names separated by commas.

When you are sorting by multiple columns, the sort sequence is exactly as specified. Using the output in the example above, the products are sorted by the prod_name column only when multiple rows have the same prod_price value. If all the values in the prod_price column had been unique, no data would have been sorted by prod_name.

Sorting by Column Position

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;

Instead of specifying column names, the relative positions of selected columns in the SELECT list can be used. ORDER BY 2 means sort by the second column in the SELECT list, the prod_price column. ORDER BY 2, 3 means sort by prod_price and then by prod_name.

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, prod_name;

You can mix and match actual column names and relative column positions in a single statement if needed.

Specifying Sort Direction

By default, data sorting is by ascending sort orders (from A to Z). If we want it be in descending order (from Z to A), use DESC keyword.

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

The DESC keyword only applies to the column name that directly precedes it. If you want to sort descending on multiple columns, be sure each column has its own DESC keyword.

It is worth noting that DESC is short for DESCENDING, and both keywords may be used. The opposite of DESC is ASC (or ASCENDING), which may be specified to sort in ascending order. In practice, however, ASC is not usually used because ascending order is the default sequence (and is assumed if neither ASC nor DESC are specified).