💡 Ask Tutor

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:

SQL
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)

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

Expected Output (example):

nameprice
Laptop Pro1200.00
Mechanical Keyboard99.99
USB-C Hub39.00
Webcam 1080p65.00
Wireless Mouse25.50

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

Example 2: Sort users by created_at (oldest first)

SQL
SELECT username, created_at FROM users
ORDER BY created_at ASC;

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

usernamecreated_at
john_doe2025-06-29 21:00:00
admin_user2025-06-29 21:05:00
jane_doe2025-06-29 21:10:00

2. Sorting by a Single Column (Descending)

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

SQL
SELECT name, price FROM products
ORDER BY price DESC;

Expected Output (example):

nameprice
Laptop Pro1200.00
Mechanical Keyboard99.99
Webcam 1080p65.00
USB-C Hub39.00
Wireless Mouse25.50

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)

SQL
SELECT user_id, username FROM users
ORDER BY user_id DESC;

Expected Output (example):

user_idusername
3jane_doe
2admin_user
1john_doe

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:

SQL
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:

SQL
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:

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

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

namecategoryprice
USB KeyboardAccessories30.00
Gaming MonitorElectronics350.00
Gaming MouseElectronics75.00
Laptop ProElectronics1200.00
Mechanical KeyboardNULL99.99
Webcam 1080pNULL65.00
USB-C HubNULL39.00
Wireless MouseNULL25.50

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)

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

Expected Output (example):

namepriceis_active
Laptop Pro1200.001
Gaming Monitor350.001
Mechanical Keyboard99.991
Gaming Mouse75.001
USB-C Hub39.001
Wireless Mouse25.501
USB Keyboard30.001

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.
SQL
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.

📘 Continue to the Next Chapter

Limiting Results with LIMIT / TOP

Learn how to control the number of records returned from your SQL queries using the LIMIT and TOP clauses effectively.

➡️

🚀 Explore Popular Learning Tracks