SQL ORDER BY Clause

SQL: ORDER BY Clause

The SQL ORDER BY clause allows you to sort the records in your result set. The SQL ORDER BY clause can only be used in SQL SELECT statements.

The syntax for the SQL ORDER BY clause is:

SELECT columns

FROM tables

WHERE predicates

ORDER BY column ASC/DESC;

The SQL ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, it is sorted by ASC.

ASC indicates ascending order. (default)
DESC indicates descending order.

SQL ORDER BY - Sorting without using ASC/DESC attribute example

The SQL ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted fromthe SQL ORDER BY clause, the sort order is defaulted to ASC or ascending order.

For example:

SELECT supplier_city

FROM suppliers

WHERE supplier_name = 'IBM'

ORDER BY supplier_city;

This SQL ORDER BY example would return all records sorted by the supplier_city field in ascending order and would be equivalent to the following SQL ORDER BY clause:

SELECT supplier_city

FROM suppliers

WHERE supplier_name = 'IBM'

ORDER BY supplier_city ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

SQL ORDER BY - 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 = 'IBM'

ORDER BY supplier_city DESC;

This SQL ORDER BY example would return all records sorted by the supplier_city field in descending order.

SQL ORDER BY - Sorting by relative position example

You can also use the SQL 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 = 'IBM'

ORDER BY 1 DESC;

This SQL 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 SQL ORDER BY clause:

SELECT supplier_city

FROM suppliers

WHERE supplier_name = 'IBM'

ORDER BY supplier_city DESC;

SQL ORDER BY - Using both ASC and DESC attributes together

When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single SQL SELECT statement.

For example:

SELECT supplier_city, supplier_state

FROM suppliers

WHERE supplier_name = 'IBM'

ORDER BY supplier_city DESC, supplier_state ASC;

This SQL 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.