How to Use the ORDER BY Clause With Multiple Columns in MySQL

1. the ORDER BY Clause in MySQL
2. Use the ORDER BY Clause With Multiple Columns in MySQL

Today, we will understand using the ORDER BY clause with multiple columns in MySQL.

the ORDER BY Clause in MySQL

The order of records (rows) in the output is unspecified whenever we retrieve the data from a table using the SELECT statement. To get it sorted, we can use the ORDER BY clause in conjunction with the SELECT statement.

Using the ORDER BY clause, we can sort the data, retrieved from one or multiple columns, into ascending or descending order by using ASC (for ascending) and DESC (for descending) options.

If we don’t specify the option (ASC or DESC), the ORDER BY clause sorts the data in ascending order using ASC, a default option. Therefore, we get the same results using the following queries because both are equivalent.

#following both queries are equivalent
SELECT selectlist FROM tablename ORDER BY column1;
SELECT selectlist FROM tablename ORDER BY column1 ASC;

We write only one column after the ORDER BY clause to sort the data using one column. Otherwise, write multiple columns separated by a comma.

See the following lines of code.

#sort data in ascending order by using one column
SELECT selectlist FROM tablename ORDER BY column1 ASC;

#sort data in descending order by using multiple columns
SELECT selectlist FROM tablename ORDER BY column1 DESC, column2 DESC;

Use the ORDER BY Clause With Multiple Columns in MySQL

To learn the use of the ORDER BY clause, we must have a table. For that reason, we create a table named tb_students in the db_ms20 database, where tb is a prefix for tables and db for the database.

It is not compulsory but an excellent approach to differentiate between tables and databases with the exact names.

Example code (to create and populate the table tb_students):

#create a database
CREATE SCHEMA `db_ms20` ;

#create a table
CREATE TABLE `db_ms20`.`tb_students` (
    `ID` INT NOT NULL AUTO_INCREMENT,
    `FIRSTNAME` VARCHAR(45) NOT NULL,
    `LASTNAME` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`ID`));

#insert data into a table
INSERT INTO db_ms20.tb_students (FIRSTNAME, LASTNAME) VALUES
('Thomas', 'Christoper'),
('Thomas', 'Jorge'),
('Mehvish', 'Ashiq'),
('Johny', 'James'),
('Daniel', 'Glass'),
('Debbra', 'Herring'),
('Daniel', 'Costner');

#select all data from the table
SELECT * FROM db_ms20.tb_students;

Output (The data of the tb_students table):

| ID   | FIRSTNAME | LASTNAME   |
| ---- | --------- | ---------- |
| 1    | Thomas    | Christoper |
| 2    | Thomas    | Jorge      |
| 3    | Mehvish   | Ashiq      |
| 4    | Johny     | James      |
| 5    | Daniel    | Glass      |
| 6    | Debbra    | Herring    |
| 7    | Daniel    | Costner    |

Let’s use the ORDER BY clause with ASC and DESC options to sort the result set into ascending or descending order.

Example code:

SELECT * FROM db_ms20.tb_students ORDER BY FIRSTNAME, LASTNAME;

# we can use the following query as an alternative
# for getting the same output
SELECT * FROM db_ms20.tb_students ORDER BY FIRSTNAME ASC, LASTNAME ASC;

Output:

The ORDER BY clause sorts the data in ascending order using the FIRSTNAME column. Further, it sorts the already sorted data in ascending order using the LASTNAME column.

The worth noting point is how the data is sorted using multiple columns.

The data will be sorted in two steps where we are ordering the data using two columns.

1. The result set is sorted in ascending order using the FIRSTNAME column.
2. If two or more values in the FIRSTNAME column are the same, then the LASTNAME will be sorted in ascending order for those records. See the red boxes in the above output.

Similarly, we can sort the data in descending order by replacing the ASC with the DESC option with each column. Following is another example where we need to sort the FIRSTNAME column in descending order and the LASTNAME column in ascending order.

Example code:

SELECT * FROM db_ms20.tb_students ORDER BY FIRSTNAME DESC, LASTNAME ASC;

Output:

Here, the ORDER BY will sort the data as follows:

1. First, the data will be sorted in descending order using the FIRSTNAME column.

2. Second, the already sorted data will be sorted in ascending order using the LASTNAME column without changing the values’ order in the FIRSTNAME column. That means the LASTNAME will be sorted in ascending order if two or more values in the FIRSTNAME column are the same.

   See the red rectangles to understand.

Remember, if you have NULL values in your dataset, then the NULLS FIRST (places non-NULL values after the NULL values) and NULLS LAST (places NULL values after the non-NULL values) options can be used as follows:

SELECT selectlist FROM tablename
ORDER BY
column1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
column2 [ASC | DESC] [NULLS FIRST | NULLS LAST];