SQL ORDER BY

A query's results come back in no particular guaranteed order unless you ask for one. ORDER BY sets that order, useful for things like "top earners first" or an alphabetical list.

Learning Objectives

Sort query results ascending or descending.
Sort by more than one column at once.
Understand why relying on a table's "natural" order is unsafe.

Unordered Result

No guaranteed order

ORDER BY column

ASC or DESC

Sorted Result

Predictable, every time

ASC and DESC

ASC (ascending, low to high) is the default if you don't specify. DESC sorts descending, high to low.

MySQL / PostgreSQL / SQLite / SQL Server

SELECT first_name, salary
FROM employees
ORDER BY salary DESC;

-- Result:
-- first_name | salary
-- Grace      | 98000
-- Ada        | 95000
-- Margaret   | 87000

Sorting by Multiple Columns

This groups rows by department alphabetically, and within each department, sorts highest salary first. The second column only matters for breaking ties in the first, it doesn't apply both directions to everything at once.

MySQL / PostgreSQL / SQLite / SQL Server

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

Why "Natural" Order Isn't Guaranteed

NOTE

Without ORDER BY, a database is free to return rows in whatever order is most efficient internally, insertion order, physical storage order, or something else entirely, and that order can even change between runs of the same query. If an order matters at all, write ORDER BY explicitly, don't rely on what a query happens to return today.

Try It Yourself

Write a query listing every employee's first_name and department, sorted by department alphabetically, and within each department, alphabetically by first_name.

Answer

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

Frequently Asked Questions

Can I sort by a column I'm not selecting?

Yes, same as WHERE, ORDER BY works on the table's columns directly, not just the ones listed in SELECT.

Where does NULL sort by default?

It varies by database, covered in detail in the SQL NULL Values lesson, MySQL and SQLite sort NULL first by default in ascending order, PostgreSQL and SQL Server sort it last.

Common Beginner Mistakes

Assuming a table without ORDER BY comes back in insertion order

It often does, by coincidence, on a small table, which is exactly what makes this mistake so easy to make and so surprising when it eventually breaks on a larger table or after an update.

Expecting ORDER BY department, salary DESC to sort both columns descending

DESC applies only to the column written next to it. This sorts department ascending (the default) and salary descending, not both, write ORDER BY department DESC, salary DESC if that's the actual intent.

Best Practices

Write ORDER BY explicitly any time row order actually matters to your application, never assume a default order.
Be explicit about ASC/DESC on every column in a multi-column sort, rather than assuming one setting applies to all of them.
Add a tiebreaker column (like an id) when the main sort column can repeat, so the order stays fully predictable.

Interview Questions

If two rows tie on the ORDER BY column, is their relative order guaranteed?

No. ORDER BY only guarantees order for the column(s) specified, rows that tie can come back in any order relative to each other, and that order isn't guaranteed to stay the same between runs. Add a second ORDER BY column to make it fully deterministic.

Write a query listing employees by department alphabetically, and within each department, youngest hire first.

SELECT first_name, department, hire_date FROM employees ORDER BY department ASC, hire_date DESC; — assuming the most recent hire_date is the youngest hire, DESC sorts most recent first.

Summary

This lesson covered sorting ascending (the default) and descending with ASC/DESC, sorting by multiple columns, and why a table's order is never safe to assume without an explicit ORDER BY.

What's Next?

The next lesson covers INSERT INTO, adding new rows to a table.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.