# SQL ORDER BY: Sort Results in Ascending or Descending Order

- URL: https://betteranalyst.com/sql/order-by

SQL guide · Querying

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

- [SQL SELECT](https://betteranalyst.com/sql/select)
- [SQL SELECT TOP / LIMIT](https://betteranalyst.com/sql/select-top)
- [SQL GROUP BY](https://betteranalyst.com/sql/group-by)
- [SQL SELECT DISTINCT](https://betteranalyst.com/sql/distinct)
- [SQL WHERE](https://betteranalyst.com/sql/where)
- [SQL UNION](https://betteranalyst.com/sql/union)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)