SQL ORDER BY

Without ORDER BY, a database returns rows in whatever order is cheapest for it, and that order can change from one run to the next. ORDER BY sorts the result so it is predictable: highest salary first, newest order first, names A to Z.

ORDER BY syntax

SELECT columnsFROM table_nameORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

ASC (ascending: A to Z, small to large, old to new) is the default. DESC reverses it.

Sample data

Sort ascending

SELECT first_name, salaryFROM employeesORDER BY salary;

Sort descending

SELECT first_name, hire_dateFROM employeesORDER BY hire_date DESC;

Sort by several columns

Sort by department, and inside each department by salary from high to low:

SELECT dept_id, first_name, salaryFROM employeesORDER BY dept_id, salary DESC;

Ties are common. Priya and Karan both earn 72,000; add a second key so their order is defined:

SELECT first_name, salaryFROM employeesWHERE dept_id = 1ORDER BY salary DESC, first_name;

Sort by an expression or alias

ORDER BY runs after SELECT, so it can use a column alias:

SELECT first_name, salary, commission,       salary + IFNULL(commission, 0) AS total_payFROM employeesWHERE dept_id = 2ORDER BY total_pay DESC;

You can also sort by a column that is not in the SELECT list:

SELECT product_nameFROM productsORDER BY price DESCLIMIT 3;

Where do NULLs go?

MySQL sorts NULL before all values in ASC and after them in DESC. To put NULLs last in an ascending sort, sort by column IS NULL first:

SELECT first_name, commissionFROM employeesORDER BY commission IS NULL, commission;

A custom sort order with FIELD() and CASE

Show orders by status in a business order: Pending first, then Shipped, Delivered, Cancelled:

SELECT order_id, statusFROM ordersORDER BY FIELD(status, 'Pending', 'Shipped', 'Delivered', 'Cancelled'), order_id;

FIELD() is MySQL-specific. The portable way is a CASE expression:

SELECT order_id, statusFROM ordersORDER BY CASE status           WHEN 'Pending'   THEN 1           WHEN 'Shipped'   THEN 2           WHEN 'Delivered' THEN 3           ELSE 4         END,         order_id;

SELECT product_name, category, priceFROM productsORDER BY category, price;

SELECT customer_name, joined_onFROM customersORDER BY joined_on DESC;

ORDER BY FAQ