How to Order Results by Multiple Columns in SQL

1. Introduction

SQL is a key tool in data analysis, enabling us to access and manage data in our databases. Furthermore, we can filter, join, and summarize data with SQL, making it easier to analyze huge amounts of data.

In this tutorial, we’ll learn how to order results by multiple columns in SQL.

2. Environment Setup

Each SQL query returns a result set based on specified criteria. Optionally, we can select an order for the resulting dataset using the ORDER BY clause. This clause allows us to sort the rows returned by a query based on one or more columns.

When we specify multiple column names to the ORDER BY clause, the later columns are used to sort rows that are equal according to the earlier columns. Each column name can be followed by an optional keyword, ASC or DESC, to define the sort direction to either ascending or descending. Ascending order is the default and puts smaller values first.

We’ll use the Faculty table from the University database to show the ordering results by multiple columns in SQL.

Let’s insert a few rows into the Faculty table:

INSERT INTO Faculty (id, name, national_id, position, start_date, end_date, department_id) VALUES
  (1, 'Anubha Gupta', 1018901231, 'Professor', '2010-1-11', '2027-3-11', 2),
  (2, 'Anubha Gupta', 1018901231, 'Associate Professor', '2007-1-9', '2010-1-10', 2),
  (3, 'Anubha Gupta', 1018901231, 'Assistant Professor', '2004-5-11', '2007-1-8', 2),
  (21, 'Peter Pan', 2130989011, 'Professor', '2007-5-11', NULL, 2),
  (22, 'Peter Pan', 2130989011, 'Associate Professor', '2004-7-11', '2007-5-10', 2),
  (23, 'Peter Pan', 2130989011, 'Assistant Professor', '2001-5-11', '2004-7-10', 2),
  (33, 'Nando de Fretas', 3130901341, 'Associate Professor', '2023-3-11', '2025-4-8', 2),
  (34, 'Nando de Fretas', 3130901341, 'Assistant Professor', '2020-5-17', '2023-3-10', 2),
  (41, 'Robert Ludloo', 3031201441, 'Assistant Professor', '2017-5-11', NULL, 2),
  (42, 'Robert Ludloo', 3031201441, 'Teaching Assistant', '2014-5-11', '2017-5-10', 2);

This SQL statement inserts a total of 10 rows into the Faculty table.

3. Basic Ordering

Let’s start with a basic ordering using two columns:

SELECT * FROM Faculty ORDER BY position, name;

This query selects and returns all rows from the Faculty table ordered by two columns: position and name. First, this query retrieves all the rows from the Faculty table. Then, the result set is ordered by the position column. If we have multiple rows with the same value in the position column, they are sorted based on the values in the name column.

We can also use the column indexes instead of column names in the ORDER BY clause:

SELECT * FROM Faculty ORDER BY 4, 2;

Furthermore, we can change the sorting order of one or more columns:

SELECT * FROM Faculty ORDER BY position ASC, name DESC;

This query returns the same number of rows but in a different order. The retrieved rows are first sorted by the position column in ascending order, and then, rows with the same position value are further sorted by the name column in descending order.

Likewise, we can specify additional column names followed by the optional sorting order (ASC or DESC) under the ORDER BY clause to order the results by multiple columns.

4. Advanced Ordering

In SQL, we can set the order of rows based on conditions. The CASE clause provides a way to construct generic conditional expressions in SQL, similar to the if-else conditional statement in other programming languages.

Let’s understand the CASE clause by an example:

SELECT * FROM Faculty ORDER BY position,
CASE WHEN position = "Professor" THEN name
ELSE start_date END DESC;

Let’s see the results:

+----+-----------------+-------------+---------------------+------------+------------+---------------+
| id | name            | national_id | position            | start_date | end_date   | department_id |
+----+-----------------+-------------+---------------------+------------+------------+---------------+
| 34 | Nando de Fretas |  3130901341 | Assistant Professor | 2020-05-17 | 2023-03-10 |             2 |
| 41 | Robert Ludloo   |  3031201441 | Assistant Professor | 2017-05-11 | NULL       |             2 |
|  3 | Anubha Gupta    |  1018901231 | Assistant Professor | 2004-05-11 | 2007-01-08 |             2 |
| 23 | Peter Pan       |  2130989011 | Assistant Professor | 2001-05-11 | 2004-07-10 |             2 |
| 33 | Nando de Fretas |  3130901341 | Associate Professor | 2023-03-11 | 2025-04-08 |             2 |
|  2 | Anubha Gupta    |  1018901231 | Associate Professor | 2007-01-09 | 2010-01-10 |             2 |
| 22 | Peter Pan       |  2130989011 | Associate Professor | 2004-07-11 | 2007-05-10 |             2 |
| 21 | Peter Pan       |  2130989011 | Professor           | 2007-05-11 | NULL       |             2 |
|  1 | Anubha Gupta    |  1018901231 | Professor           | 2010-01-11 | 2027-03-11 |             2 |
| 42 | Robert Ludloo   |  3031201441 | Teaching Assistant  | 2014-05-11 | 2017-05-10 |             2 |
+----+-----------------+-------------+---------------------+------------+------------+---------------+
10 rows in set (0.00 sec)

This query returns a result set of 10 rows primarily sorted by the position column. If the rows have the same value for the position column, then they are further sorted by the CASE WHEN logic. If the value of the position column is Professor, then rows are sorted by the name column in descending order; otherwise, they are sorted by the start_date column in descending order.

We can also specify multiple conditions using the WHEN clause:

SELECT * FROM Faculty ORDER BY position,
CASE WHEN position = "Professor" THEN name
WHEN position = "Assistant Professor" THEN national_id
ELSE start_date END DESC;

This query implements one additional expression that sorts the rows based on the national_id column if the value of position is Assistant Professor.

The CASE statement is only used to evaluate which column the ORDER BY clause uses to sort. The ASC or DESC keywords must appear outside of any CASE statements.

Similarly, we can add as many conditions as we want to order the results based on one or multiple columns.

5. Conclusion

In this article, we learned different ways to order results by multiple columns in SQL.

First, we briefly discussed the ORDER BY clause and its usage in ordering the results. Then, we used column names to order the results by two columns. We also tested the usage of column indices instead of column names to order the results. Furthermore, we used the CASE clause to construct conditional expressions and sort the results based on those conditions.