Chapter 8: Sorting Data with ORDER BY

When you retrieve data using SELECT, the order in which the rows are returned is not guaranteed. The database might return rows in the physical order they were stored, or based on an optimized retrieval path, which can seem arbitrary. For displaying data on a website (e.g., a list of products by price, blog posts by date, or users alphabetically), you need to control this order.

This is where the ORDER BY clause comes in. It allows you to sort your result set based on the values in one or more columns, either in ascending or descending order. This chapter will teach you how to use ORDER BY to present your data exactly how you need it.

The Purpose of ORDER BY

The ORDER BY clause is used to sort the result-set of a SELECT query. It’s typically the last clause in a SELECT statement (before LIMIT, which we’ll cover next).

Basic Structure:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort_by [ASC|DESC], another_column_to_sort_by [ASC|DESC], ...;

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_to_sort_by [ASC|DESC], another_column_to_sort_by [ASC|DESC], ...;

• column_to_sort_by: The name of the column you want to sort the results by.
• ASC: (Optional) Specifies ascending order (A-Z for text, 0-9 for numbers, oldest to newest for dates). This is the default if neither ASC nor DESC is specified.
• DESC: Specifies descending order (Z-A for text, 9-0 for numbers, newest to oldest for dates).

Sorting Order

• Numbers: Sorted numerically (e.g., 1, 2, 10, 100).
• Text (Strings): Sorted alphabetically (A-Z).
• Dates/Times: Sorted chronologically (earliest to latest).
• NULL Values: In MySQL/MariaDB, NULL values are treated as the lowest possible value for sorting ASC and the highest possible value for sorting DESC.

SQL Syntax & Examples

Let’s continue using our scriptbuzz_db. Remember to run USE scriptbuzz_db; first.

1. Sorting by a Single Column (Ascending)

Example 1: Sort products by name in ascending order (A-Z)

SELECT name, price FROM products
ORDER BY name ASC;
-- Or simply: ORDER BY name; (ASC is default)

SELECT name, price FROM products
ORDER BY name ASC;
-- Or simply: ORDER BY name; (ASC is default)

Expected Output (example):

What it does: Retrieves product names and prices, sorted alphabetically by name.

Example 2: Sort users by created_at (oldest first)

SELECT username, created_at FROM users
ORDER BY created_at ASC;

SELECT username, created_at FROM users
ORDER BY created_at ASC;

Expected Output (example – dates will vary based on your insertion times):

2. Sorting by a Single Column (Descending)

Example 3: Sort products by price in descending order (highest to lowest)

SELECT name, price FROM products
ORDER BY price DESC;

SELECT name, price FROM products
ORDER BY price DESC;

Expected Output (example):

What it does: Retrieves product names and prices, sorted from the highest price to the lowest.

Example 4: Sort users by user_id (newest first, assuming IDs increment)

SELECT user_id, username FROM users
ORDER BY user_id DESC;

SELECT user_id, username FROM users
ORDER BY user_id DESC;

Expected Output (example):

3. Sorting by Multiple Columns

You can specify multiple columns in the ORDER BY clause. The database will first sort by the first column. If there are ties (rows with the same value in the first sort column), it will then use the second column to sort those tied rows, and so on.

Syntax:

SELECT ... FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

SELECT ... FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Example 5: Sort products by category (ASC), then by price (DESC) within each category

Let’s add a couple more products to make this example more meaningful:

INSERT INTO products (name, category, description, price, stock_quantity) VALUES
('Gaming Monitor', 'Electronics', '144Hz high refresh rate.', 350.00, 20),
('USB Keyboard', 'Accessories', 'Basic wired keyboard.', 30.00, 150),
('Gaming Mouse', 'Electronics', 'High DPI gaming mouse.', 75.00, 40);

INSERT INTO products (name, category, description, price, stock_quantity) VALUES
('Gaming Monitor', 'Electronics', '144Hz high refresh rate.', 350.00, 20),
('USB Keyboard', 'Accessories', 'Basic wired keyboard.', 30.00, 150),
('Gaming Mouse', 'Electronics', 'High DPI gaming mouse.', 75.00, 40);

Now, run the query:

SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;

SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;

Expected Output (example – assuming some NULL categories and added products):

What it does:

1. All rows are first sorted by category in ascending order (NULLs typically come first or last depending on database, but in MySQL, NULL values sort before non-NULL values in ASC order).
2. For rows that have the same category value (e.g., ‘Electronics’), those specific rows are then sorted by price in descending order.

4. Combining WHERE and ORDER BY

The ORDER BY clause always comes after the WHERE clause.

Example 6: Select active products, sort by price (DESC)

SELECT name, price, is_active FROM products
WHERE is_active = 1
ORDER BY price DESC;

SELECT name, price, is_active FROM products
WHERE is_active = 1
ORDER BY price DESC;

Expected Output (example):

What it does: First, filters for only active products, then sorts the resulting active products by price from highest to lowest.

Common Pitfalls/Tips

• Order of Clauses: FROM -> WHERE -> ORDER BY. Mixing them up will cause syntax errors.
• Performance: Sorting large datasets can be resource-intensive. If you frequently sort by a particular column, consider adding an index to that column (we’ll cover indexes later).
• Sorting by Aliased Columns: You can use column aliases in the ORDER BY clause.

SELECT name AS product_name, price AS current_price
FROM products
ORDER BY current_price DESC;

SELECT name AS product_name, price AS current_price
FROM products
ORDER BY current_price DESC;

• Consistent Sorting: For user-facing lists, always apply an ORDER BY clause to ensure consistent results. Without it, the order is not guaranteed and can change between queries or database versions.

Notes:

• The ORDER BY clause sorts the result set of a SELECT query.
• You can sort by one or multiple columns.
• ASC for ascending order (default), DESC for descending order.
• When sorting by multiple columns, the second column sorts rows that have identical values in the first column, and so on.
• ORDER BY comes after FROM and WHERE clauses.
• Sorting is crucial for presenting organized and meaningful data to users.

Small Exercise

Using your scriptbuzz_db:

1. Select all users, sorted alphabetically by their username in ascending order.
2. Select all products, sorted first by is_active in descending order (so active products come first), and then by name alphabetically within each is_active group.
3. If you have an orders table, select all orders, sorted by order_date from newest to oldest.
4. If you have a posts table, select all posts, filtered to show only published posts, and then sorted by created_at from newest to oldest.

You’re now a master of retrieving and organizing data! Next, we’ll learn how to control how many results you get.

🚀 Explore Popular Learning Tracks

Learn Python

Learn AWS

Learn Git & GitHub