sql_order-by

SQL ORDER BY Clause

The ORDER BY clause is used to sort the rows in the result set of a query. You can sort by one or more columns, in ascending or descending order.

The SQL ORDER BY Clause Explained

By default, the ORDER BY clause sorts records in ascending order (ASC). To sort records in descending order, use the DESC keyword.

If ORDER BY is not specified, the database system returns rows in an undefined order, which can vary depending on query execution plans, data storage, or even database version. Always use ORDER BY if you need a guaranteed sort order.

SQL ORDER BY Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column_to_sort_by_1 [ASC|DESC], column_to_sort_by_2 [ASC|DESC], ...;

• ASC: Sorts in ascending order (A to Z, 0 to 9). This is the default.

• DESC: Sorts in descending order (Z to A, 9 to 0).

You can also sort by column position (e.g., ORDER BY 2 DESC to sort by the second column in the SELECT list), but this is generally discouraged for maintainability as changes to the SELECT list can break the sort logic. Using column names is preferred.

Demo Table Context

For the following examples, let’s assume a Customers table with columns such as CustomerID, CustomerName, City, and Country.

ORDER BY Single Column Example

The following SQL statement selects all customers from the Customers table, sorted by the Country column in ascending order (default):

SELECT CustomerName, City, Country
FROM Customers
ORDER BY Country;

ORDER BY DESC Example

The following SQL statement selects all customers from the Customers table, sorted by the Country column in descending order:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY Country DESC;

ORDER BY Multiple Columns Example

The following SQL statement selects all customers, sorted first by Country (ascending) and then, within each country, by CustomerName (ascending):

SELECT CustomerName, City, Country
FROM Customers
ORDER BY Country, CustomerName;

This means all customers from ‘Argentina’ will appear before ‘Brazil’, and within ‘Argentina’, customers will be sorted alphabetically by name.

ORDER BY Multiple Columns with Mixed Order Example

The following SQL statement selects all customers, sorted by Country in ascending order, and then by City in descending order within each country:

SELECT CustomerName, City, Country
FROM Customers
ORDER BY Country ASC, City DESC;

So, for ‘Germany’, cities like ‘München’ would appear before ‘Berlin’ if they exist for German customers.

Sorting by Expressions and NULL Values

You can also sort by expressions, for example, ORDER BY LENGTH(CustomerName). The handling of NULL values in sorting (NULLS FIRST or NULLS LAST) can vary by database or be explicitly controlled in some systems (e.g., PostgreSQL and Oracle allow NULLS FIRST/NULLS LAST modifiers). By default, many systems treat NULL as lower than any non-null value (so NULLS FIRST for ASC) or higher (so NULLS LAST for ASC). Check your RDBMS documentation for specifics.

Practical Use Cases:

• Displaying products from cheapest to most expensive.

• Listing users alphabetically by last name.

• Showing the most recent comments first.