Sorting In MySQL Using Order By Clause

This tutorial is part of the series Learn Basic SQL Queries Using MySQL. In this tutorial, we will discuss SQL queries to perform sorting of the table rows in MySQL using the ORDER BY clause with the keywords ASC or DESC to sort in ascending or descending order.

Order By Query

The ORDER By clause can be used to perform sorting on the table rows either in ascending or descending order.

# ORDER BY - Syntax - Default Ascending
SELECT * FROM `table_name` ORDER BY `column_name`;

# ORDER BY - Syntax - Ascending
SELECT * FROM `table_name` ORDER BY `column_name` ASC;

# ORDER BY - Syntax - Descending
SELECT * FROM `table_name` ORDER BY `column_name` DESC;

Query Explanation

The ORDER BY clause can be used to sort the rows by specifying the column to be used for sorting. We can also use the ORDER  BY clause with WHERE to perform the filter or search operations with sorting.

We have to specify at least one column using the ORDER BY clause to sort the rows. We can also specify the order to sort the rows either in ascending or descending order using ASC or DESC keywords respectively.

We can also apply multiple sort operations where the subsequent sorting can be applied following the results returned by the sort operations which comes first.

Examples

This section provides examples to perform sorting using the ORDER BY clause with and without WHERE clause. Use the below-mentioned query to create the user table having the id, first name, last name, and active columns to store user data.

# Create the User Table
CREATE TABLE `enterprise`.`user` (
  `user_id` BIGINT NOT NULL,
  `first_name` VARCHAR(45),
  `last_name` VARCHAR(45),
  `active` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`));

The below-mentioned query can be used to insert data in the user table.

# Insert Rows - All Columns
INSERT INTO `user` ( `user_id`, `first_name`, `last_name`, `active` ) VALUES 
    ( 1, 'John', 'Smith', 1 ),
    ( 2, 'Rick', 'Jones', 1 ),
    ( 3, 'John', 'Ponting', 0 ),
    ( 4, 'Harsh', 'Upadhyay', 1 ),
    ( 5, 'Tajwinder', 'Singh', 0 );

The above-mentioned query will insert 5 rows in the table having the id, first name, last name, and active columns to represent 5 different users.

Now we will use the ORDER BY clause without the WHERE clause to sort the rows using the first name or last name columns of the user table. It can be done using the ORDER BY clause in MySQL as shown below.

# ORDER BY - Ascending
SELECT * FROM `user` ORDER BY `first_name`;
SELECT * FROM `user` ORDER BY `first_name` ASC;

# Result
4	Harsh		Upadhyay	1
1	John		Smith		1
3	John		Ponting		0
2	Rick		Jones		1
5	Tajwinder	Singh		0

# ORDER BY - Descending
SELECT * FROM `user` ORDER BY `first_name` DESC;

# Result
5	Tajwinder	Singh		0
2	Rick		Jones		1
1	John		Smith		1
3	John		Ponting		0
4	Harsh		Upadhyay	1

# ORDER BY - Ascending - Multiple
SELECT * FROM `user` ORDER BY `first_name`, `last_name`;

# Result
4	Harsh		Upadhyay	1
3	John		Ponting		0
1	John		Smith		1
2	Rick		Jones		1
5	Tajwinder	Singh		0

The above-mentioned queries perform sorting in ascending and descending order using the ORDER BY clause. Now we will sort the rows with the WHERE clause as shown below.

# ORDER BY - Ascending - Filter active users
SELECT * FROM `user` WHERE `active` = 1 ORDER BY `first_name`;

# Result
4	Harsh	Upadhyay	1
1	John	Smith		1
2	Rick	Jones		1

# ORDER BY - Descending - Filter active users
SELECT * FROM `user` WHERE `active` = 1 ORDER BY `first_name` DESC;

# Result
2	Rick	Jones		1
1	John	Smith		1
4	Harsh	Upadhyay	1

This is how we can use the ORDER BY clause with and without the WHERE clause to sort the rows of the table for the specific columns.