MySQL ORDER BY

Introduction

When retrieving data from a database, you often need to present it in a specific order. MySQL's ORDER BY clause allows you to sort your query results based on one or multiple columns, making your data more organized and easier to analyze. Whether you need to display products by price, list employees by hire date, or sort students by name, the ORDER BY clause is your go-to solution.

In this tutorial, we'll explore how to use MySQL's ORDER BY clause to sort your query results effectively.

Basic Syntax

The basic syntax of the ORDER BY clause is:

sql

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

Where:

• column1, column2, ... are the columns you want to sort by
• ASC sorts in ascending order (default if not specified)
• DESC sorts in descending order

Sorting in Ascending Order

By default, the ORDER BY clause sorts the results in ascending order (A to Z, 0 to 9).

Example

Let's assume we have a customers table:

sql

SELECT customer_id, first_name, last_name, city
FROM customers
ORDER BY last_name;

Output:

+-------------+------------+-----------+-------------+
| customer_id | first_name | last_name | city        |
+-------------+------------+-----------+-------------+
| 5           | Sarah      | Adams     | Chicago     |
| 3           | Michael    | Brown     | Los Angeles |
| 1           | John       | Doe       | New York    |
| 4           | Emily      | Johnson   | Miami       |
| 2           | Jane       | Smith     | Boston      |
+-------------+------------+-----------+-------------+

In this example, customers are sorted alphabetically by their last names.

Sorting in Descending Order

To sort in descending order (Z to A, 9 to 0), use the DESC keyword.

Example

sql

SELECT product_name, price
FROM products
ORDER BY price DESC;

Output:

+-------------------+--------+
| product_name      | price  |
+-------------------+--------+
| Premium Laptop    | 1299.99|
| Smartphone        | 899.99 |
| Digital Camera    | 599.99 |
| Wireless Headset  | 129.99 |
| USB Drive         | 24.99  |
+-------------------+--------+

This query returns products sorted from highest to lowest price.

Sorting by Multiple Columns

You can sort results based on multiple columns. MySQL will sort by the first column, and if there are ties, it will sort by the second column, and so on.

Example

sql

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

Output:

+------------+-----------+------------+----------+
| first_name | last_name | hire_date  | salary   |
+------------+-----------+------------+----------+
| Robert     | Johnson   | 2018-05-12 | 85000.00 |
| Maria      | Garcia    | 2019-09-23 | 72000.00 |
| David      | Lee       | 2020-01-15 | 68000.00 |
| Alice      | Wong      | 2017-11-30 | 92000.00 |
| James      | Smith     | 2019-03-08 | 78000.00 |
+------------+-----------+------------+----------+

This query first sorts employees by department in ascending order, and within each department, it sorts by salary in descending order.

Sorting by Column Position

You can also sort by column position (1 for the first selected column, 2 for the second, and so on). This can sometimes be more convenient but makes your queries less readable.

Example

sql

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY 3; -- Sort by the third column (hire_date)

Output:

+------------+-----------+------------+
| first_name | last_name | hire_date  |
+------------+-----------+------------+
| Alice      | Wong      | 2017-11-30 |
| Robert     | Johnson   | 2018-05-12 |
| James      | Smith     | 2019-03-08 |
| Maria      | Garcia    | 2019-09-23 |
| David      | Lee       | 2020-01-15 |
+------------+-----------+------------+

In this example, the results are sorted by the hire_date column, which is the third column in the SELECT statement.

Sorting by Expressions

You can sort by the result of an expression, not just by column values.

Example

sql

SELECT product_name, price, quantity, price * quantity AS total_value
FROM inventory
ORDER BY price * quantity DESC;

Output:

+-------------------+--------+----------+-------------+
| product_name      | price  | quantity | total_value |
+-------------------+--------+----------+-------------+
| Premium Laptop    | 1299.99| 5        | 6499.95     |
| Smartphone        | 899.99 | 10       | 8999.90     |
| Digital Camera    | 599.99 | 3        | 1799.97     |
| Wireless Headset  | 129.99 | 15       | 1949.85     |
| USB Drive         | 24.99  | 50       | 1249.50     |
+-------------------+--------+----------+-------------+

Here, products are sorted by their total inventory value (price × quantity) in descending order.

NULL Values in ORDER BY

When sorting, NULL values are considered smaller than any non-NULL value. They will appear first in ascending order and last in descending order.

Example

sql

SELECT product_id, product_name, release_date
FROM products
ORDER BY release_date ASC;

Output:

+------------+-------------------+-------------+
| product_id | product_name      | release_date|
+------------+-------------------+-------------+
| 3          | Classic Model     | NULL        |
| 5          | Basic Option      | NULL        |
| 1          | Premium Laptop    | 2020-01-15  |
| 4          | Wireless Headset  | 2020-06-30  |
| 2          | Smartphone        | 2021-03-22  |
+------------+-------------------+-------------+

Products with NULL release dates appear first in ascending order.

To control how NULL values are sorted, you can use the NULLS FIRST or NULLS LAST option in some MySQL versions, or use a CASE expression:

sql

SELECT product_id, product_name, release_date
FROM products
ORDER BY 
  CASE WHEN release_date IS NULL THEN 1 ELSE 0 END, 
  release_date ASC;

Real-World Application

Let's look at a more complex real-world example. Imagine you have an e-commerce database and want to find your top-selling products by category.

sql

SELECT 
    c.category_name,
    p.product_name,
    SUM(od.quantity) as total_sold,
    SUM(od.quantity * od.unit_price) as revenue
FROM 
    order_details od
JOIN 
    products p ON od.product_id = p.product_id
JOIN 
    categories c ON p.category_id = c.category_id
WHERE 
    od.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    c.category_name, p.product_name
ORDER BY 
    c.category_name ASC,
    revenue DESC;

Output:

+----------------+------------------------+------------+-----------+
| category_name  | product_name           | total_sold | revenue   |
+----------------+------------------------+------------+-----------+
| Electronics    | Premium Laptop         | 125        | 162498.75 |
| Electronics    | Smartphone             | 210        | 188997.90 |
| Electronics    | Digital Camera         | 85         | 50999.15  |
| Home & Garden  | Coffee Maker           | 95         | 8549.05   |
| Home & Garden  | Blender                | 78         | 5849.22   |
| Sports         | Mountain Bike          | 36         | 28795.64  |
| Sports         | Tennis Racket          | 58         | 6959.42   |
+----------------+------------------------+------------+-----------+

This query first groups products by category, then sorts them alphabetically by category name, and within each category, it sorts by revenue in descending order to show the best-selling products first.

Performance Considerations

When using ORDER BY on large tables, keep these points in mind:

1. Indexing: Columns used in ORDER BY should ideally be indexed to improve performance.
2. Limit Results: Use the LIMIT clause with ORDER BY to restrict the number of rows returned.
3. Avoid Sorting Large Result Sets: Sorting large result sets can be resource-intensive.

Example with LIMIT

sql

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

This query returns only the 5 most expensive products.

Summary

The MySQL ORDER BY clause is a powerful feature that allows you to control the order of your query results. Key points to remember:

• Use ORDER BY column_name to sort in ascending order (default)
• Add DESC to sort in descending order
• Sort by multiple columns by listing them separated by commas
• NULL values appear first in ascending order and last in descending order
• You can sort by column position or expressions
• Consider performance implications when sorting large tables

Mastering ORDER BY will help you present data in a more meaningful and organized way, making your database applications more user-friendly and effective.

Exercises

1. Write a query to display all employees, sorted by last name in ascending order, and then by first name in ascending order.
2. Create a query that shows products sorted by profit margin (price - cost) in descending order.
3. Write a query to list customers by their country in ascending order, and within each country by total spending in descending order.
4. Create a query that shows orders with the most recent ones first, and for orders placed on the same day, sort by order total in descending order.

Additional Resources

• MySQL Official Documentation on ORDER BY
• MySQL Sorting and ORDER BY Optimization
• Using Indexes for Sorting

Happy querying!