SQL guide · Querying

SQL ORDER BY: Sort Results in Ascending or Descending Order

The ORDER BY clause sorts the rows of a result set. Without it, a database is free to return rows in any order, and that order can change from one run to the next as data is updated or the query plan changes. Whenever the order matters, such as for reports, pagination or picking the latest record, you must specify it with ORDER BY.

Each sort key can be followed by ASC for ascending order or DESC for descending order. Ascending is the default, so ASC is optional. You can list several keys separated by commas, and the database sorts by the first key, then breaks ties using the second, and so on. Keys can be column names, expressions, aliases defined in the select list, or column positions such as ORDER BY 2, although positions make queries fragile when the select list changes.

Sorting rules depend on data type and collation. Numbers sort numerically, dates chronologically, and text according to the collation, which controls case sensitivity and accent handling. NULL values need special care: databases disagree on whether nulls count as the smallest or the largest value. ORDER BY is evaluated last, after GROUP BY and SELECT, which is why it is the one clause that can use a column alias. It is also required to make LIMIT, TOP or FETCH FIRST return a predictable set of rows.

Basic syntax

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

Each sort key is evaluated left to right. ASC sorts smallest to largest and A to Z, DESC sorts the opposite way, and each key can have its own direction. Some databases also accept NULLS FIRST or NULLS LAST after the direction.

SQL ORDER BY examples

Sort ascending (default)

With no direction given, the sort is ascending. Products are listed from the cheapest to the most expensive.

SELECT product_name, unit_price
FROM products
ORDER BY unit_price;

Sort descending

Add DESC to reverse the order. This returns orders with the most recent date first, which is the usual pattern for activity feeds.

SELECT order_id, customer_id, order_date
FROM orders
ORDER BY order_date DESC;

Sort by multiple columns

Rows are grouped by department alphabetically, and inside each department the highest salary comes first. The second key only matters when the first keys are equal.

SELECT department, first_name, salary
FROM employees
ORDER BY department ASC, salary DESC;

Sort by an alias or expression

Because ORDER BY runs after the select list, it can use an alias. This ranks customers by the total they have spent, largest first.

SELECT customer_id, SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Where SQL dialects differ

PostgreSQL: Treats NULL as larger than any value, so nulls come last in ASC and first in DESC. Override with NULLS FIRST or NULLS LAST. Collation is set per database or column and affects text order.

MySQL: Treats NULL as the smallest value, so nulls come first in ASC. There is no NULLS LAST; emulate it with ORDER BY col IS NULL, col. Text sorting follows the column collation, and the default is case insensitive.

SQL Server: Treats NULL as the smallest value and has no NULLS FIRST option. ORDER BY is not allowed in views or subqueries unless TOP or OFFSET is also used. Pagination uses OFFSET n ROWS FETCH NEXT m ROWS ONLY from SQL Server 2012.

SQLite: Treats NULL as smaller than any other value, so nulls come first in ASC. NULLS FIRST and NULLS LAST were added in version 3.30.0. Default text comparison is binary and case sensitive.

Oracle: Treats NULL as larger than any value, so nulls come last in ASC. Supports NULLS FIRST and NULLS LAST. Use FETCH FIRST n ROWS ONLY after ORDER BY in 12c and later.

Common mistakes

  • Relying on the natural order of a table or on the primary key order. Only an explicit ORDER BY guarantees the sequence.
  • Using column numbers such as ORDER BY 3 and then changing the select list, which silently sorts by a different column.
  • Ordering by a text column that stores numbers. Values sort as strings, so 10 comes before 9 unless you cast the column to a number.
  • Using LIMIT or TOP without ORDER BY, which returns an arbitrary set of rows that may differ each time.
  • Forgetting a tie-breaker when paginating. If sort values repeat, add a unique column such as the id so rows do not move between pages.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator