Sorting Results with ORDER BY

Explanation

By default, a query doesn't promise any particular row order. If you want results sorted, you need to say so explicitly with ORDER BY.

Take the employees table:

If you want to see the highest earners first, ORDER BY salary DESC sorts the results from highest to lowest. Leave off DESC (or use ASC) and it sorts from lowest to highest instead.

Syntax

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC;

ASC (ascending, low to high) is the default, so it's optional. DESC (descending, high to low) must be written explicitly:

SELECT first_name, salary FROM employees ORDER BY salary DESC;

You can also sort by more than one column. The first column is the primary sort; the second column only matters for rows that tie on the first:

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

Interactive Example

Common Mistakes

• Assuming results come back in a "natural" order without ORDER BY. Without it, the order is undefined and can change unexpectedly.
• Putting ORDER BY before WHERE. The correct order in a query is SELECT ... FROM ... WHERE ... ORDER BY ... — ORDER BY always comes last.
• Forgetting DESC when you want highest-to-lowest. ORDER BY salary alone sorts ascending, from lowest to highest, which is the opposite of what many people expect.

Best Practices

• Always add ORDER BY explicitly whenever the order of your results matters to how you or your application will use them.
• When sorting by multiple columns, put the most important sort criterion first, and use it to break ties with the next column.
• Be explicit with ASC/DESC even when ASC is the default — it makes the query's intent obvious to anyone reading it later.

Practice Question

Using the second playground above, write a query that lists every employee's first_name and hire_date, sorted so the most recently hired employee appears first.

Summary

ORDER BY controls the order of your query results. Use ASC (the default) for ascending order and DESC for descending order, and list multiple columns separated by commas to sort by more than one criterion at once, with the first column taking priority.

FAQ