SQL ORDER BY: ASC, DESC and Multiple Columns

The most expensive products first:

SELECT name, category, price
FROM products
ORDER BY price DESC
LIMIT 5;

▶ Run this query in the SQL Playground

SELECT columns
FROM table
ORDER BY column_1 [ASC | DESC], column_2 [ASC | DESC], ...;

ORDER BY comes at the end of the query (only LIMIT follows it) and is applied to the finished result, after WHERE, GROUP BY and HAVING.

ASC (ascending) puts the lowest value first and is the default, so ORDER BY price and ORDER BY price ASC are the same. DESC (descending) reverses it.

SELECT name, price
FROM products
ORDER BY price
LIMIT 5;

▶ Run this query in the SQL Playground

List several columns and each later one breaks ties in the earlier ones. Employees by department A to Z, and within each department by salary, highest first:

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

▶ Run this query in the SQL Playground

Each column has its own direction. ORDER BY department, salary DESC means department ascending (the default) and salary descending; the DESC applies only to the column it follows.

Dates

Newest orders first is one of the most common sorts:

SELECT id, order_date, status, total
FROM orders
ORDER BY order_date DESC, id DESC
LIMIT 5;

▶ Run this query in the SQL Playground

In the Playground dates are stored as text in YYYY-MM-DD form, which sorts correctly as text. A format such as DD/MM/YYYY would not: "02/01/2024" would sort before "15/12/2023".

Text and upper/lower case

SELECT DISTINCT last_name
FROM users
ORDER BY last_name
LIMIT 6;

▶ Run this query in the SQL Playground

Whether case matters depends on the database's collation. SQLite and PostgreSQL (with most collations) sort all capital letters before lowercase ones, so "Banana" comes before "apple". MySQL and SQL Server usually ignore case. To sort without regard to case everywhere, write ORDER BY LOWER(last_name).

ORDER BY can use a column alias from SELECT, because it runs after SELECT. Here the products are sorted by their margin, a calculated column:

SELECT name, price, cost,
       ROUND(price - cost, 2) AS margin
FROM products
ORDER BY margin DESC
LIMIT 5;

▶ Run this query in the SQL Playground

With GROUP BY, ORDER BY sorts the groups. Departments by total salary cost, highest first:

SELECT department,
       COUNT(*) AS employees,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

▶ Run this query in the SQL Playground

You can also sort by a column that is not in SELECT, such as SELECT first_name FROM employees ORDER BY hire_date. The exception is SELECT DISTINCT, where most databases only allow ORDER BY on the selected columns.

A custom order with CASE

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY CASE status
           WHEN 'pending'    THEN 1
           WHEN 'processing' THEN 2
           WHEN 'shipped'    THEN 3
           WHEN 'delivered'  THEN 4
           ELSE 5
         END;

▶ Run this query in the SQL Playground

Databases disagree on where NULL sorts. SQLite, MySQL and SQL Server treat NULL as the lowest value, so NULLs come first in ascending order. PostgreSQL and Oracle treat it as the highest, so they come last:

SELECT id, status, shipped_date
FROM orders
ORDER BY shipped_date
LIMIT 4;

▶ Run this query in the SQL Playground

SQLite (3.30+), PostgreSQL and Oracle let you choose with NULLS FIRST or NULLS LAST:

SELECT id, status, shipped_date
FROM orders
ORDER BY shipped_date NULLS LAST
LIMIT 4;

▶ Run this query in the SQL Playground

MySQL and SQL Server do not support NULLS LAST; sort on a flag first instead: ORDER BY shipped_date IS NULL, shipped_date.

See LIMIT and indexes.

Five errors and how to avoid them.

SELECT * FROM orders LIMIT 10;

SELECT * FROM orders
ORDER BY order_date DESC, id DESC
LIMIT 10;

SELECT * FROM orders
ORDER BY total DESC
WHERE status = 'delivered';

SELECT * FROM orders
WHERE status = 'delivered'
ORDER BY total DESC;

ORDER BY department, salary DESC
-- meant: both descending

ORDER BY department DESC, salary DESC

SELECT name, price FROM products
ORDER BY 2 DESC;

SELECT name, price FROM products
ORDER BY price DESC;

-- dates stored as 'DD/MM/YYYY'
ORDER BY order_date_text

-- dates stored as 'YYYY-MM-DD' or a real DATE type
ORDER BY order_date

5 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the 5 most recently hired employees. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT first_name, last_name, hire_date
   FROM employees
   ORDER BY hire_date DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   DESC on a date puts the newest first.

2. 2Easy List the first 6 users alphabetically by last name, then first name. Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT last_name, first_name, city
   FROM users
   ORDER BY last_name, first_name
   LIMIT 6;

   ▶ Run this query in the SQL Playground

   first_name breaks ties between users with the same last name.

3. 3Medium Show the 5 best-rated products, and among equal ratings the ones with the most reviews first. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, rating, review_count
   FROM products
   ORDER BY rating DESC, review_count DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   Two descending sorts: rating first, review_count as the tie-breaker.

4. 4Medium Show revenue per order status, highest first. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT status, ROUND(SUM(total), 2) AS revenue
   FROM orders
   GROUP BY status
   ORDER BY revenue DESC;

   ▶ Run this query in the SQL Playground

   ORDER BY can use the alias of the aggregate.

5. 5Medium List 6 orders with their shipped date, showing shipped orders first (newest shipment first) and unshipped orders last. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, status, shipped_date
   FROM orders
   ORDER BY shipped_date IS NULL, shipped_date DESC
   LIMIT 6;

   ▶ Run this query in the SQL Playground

   shipped_date IS NULL is 0 for shipped orders and 1 for the rest, so it sorts unshipped orders to the end in any database.