MySQL ORDER BY Clause

Introduction

In this chapter, we will learn about the ORDER BY clause in MySQL. The ORDER BY clause is used to sort the result set of a query by one or more columns. Sorting can be done in ascending or descending order. This clause is often used in SELECT statements to organize the retrieved data in a meaningful way. We will cover the syntax, examples, and important considerations for using the ORDER BY clause.

Syntax

The basic syntax for the ORDER BY clause is:

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

• ASC: Sorts the result set in ascending order (default).
• DESC: Sorts the result set in descending order.

Sorting by a Single Column

Example

SELECT first_name, last_name, email
FROM students
ORDER BY last_name;

This example sorts the results by the last_name column in ascending order.

Example with DESC

SELECT first_name, last_name, email
FROM students
ORDER BY last_name DESC;

This example sorts the results by the last_name column in descending order.

Sorting by Multiple Columns

Example

SELECT first_name, last_name, email
FROM students
ORDER BY last_name ASC, first_name ASC;

This example sorts the results first by the last_name column in ascending order and then by the first_name column in ascending order.

Example with Mixed Order

SELECT first_name, last_name, email
FROM students
ORDER BY last_name ASC, enrollment_date DESC;

This example sorts the results first by the last_name column in ascending order and then by the enrollment_date column in descending order.

Full Example

Let's go through a full example where we create a table, insert data into it, and use the ORDER BY clause to sort the records.

1. Create a Database:

CREATE DATABASE school;

2. Select the Database:

USE school;

3. Create a Table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE
);

4. Insert Data into the Table:

INSERT INTO students (first_name, last_name, email, enrollment_date) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01'),
('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02'),
('Amit', 'Kumar', 'amit.kumar@example.com', '2023-07-03'),
('Neha', 'Verma', 'neha.verma@example.com', '2023-07-04');

5. Sort by Last Name:

SELECT first_name, last_name, email
FROM students
ORDER BY last_name;

Output

first_name	last_name	email
Amit	Kumar	amit.kumar@example.com
Rahul	Sharma	rahul.sharma@example.com
Priya	Singh	priya.singh@example.com
Neha	Verma	neha.verma@example.com

6. Sort by Last Name Descending:

SELECT first_name, last_name, email
FROM students
ORDER BY last_name DESC;

Output

first_name	last_name	email
Neha	Verma	neha.verma@example.com
Priya	Singh	priya.singh@example.com
Rahul	Sharma	rahul.sharma@example.com
Amit	Kumar	amit.kumar@example.com

7. Sort by Last Name and First Name:

SELECT first_name, last_name, email
FROM students
ORDER BY last_name ASC, first_name ASC;

Output

first_name	last_name	email
Amit	Kumar	amit.kumar@example.com
Rahul	Sharma	rahul.sharma@example.com
Priya	Singh	priya.singh@example.com
Neha	Verma	neha.verma@example.com

8. Sort by Last Name and Enrollment Date Descending:

SELECT first_name, last_name, email
FROM students
ORDER BY last_name ASC, enrollment_date DESC;

Output

first_name	last_name	email	enrollment_date
Amit	Kumar	amit.kumar@example.com	2023-07-03
Rahul	Sharma	rahul.sharma@example.com	2023-07-01
Priya	Singh	priya.singh@example.com	2023-07-02
Neha	Verma	neha.verma@example.com	2023-07-04

Important Considerations

• Performance: Sorting large result sets can impact performance. Use indexing on columns that are frequently used in the ORDER BY clause to improve performance.
• Null Values: Be aware of how null values are sorted. In MySQL, null values are considered lower than any non-null value.
• Consistency: Ensure consistency in sorting when using multiple columns to avoid unexpected results.

Conclusion

The ORDER BY clause is used for sorting data in MySQL queries. This chapter covered how to use the ORDER BY clause to sort data by one or more columns in ascending or descending order.