Sorting by a List of Strings in SQL

1. Overview

While working with databases, we often require sorting data for various reasons, including organizing and retrieving information efficiently. One important aspect of databases is the list of strings, which can be anything such as names, places, or product names. It’s crucial to understand how to sort these strings alphabetically or in a specific order to optimize data retrieval.

In this tutorial, we’ll learn how to sort a list of strings in MySQL, SQL Server, and PostgreSQL. For demonstration purposes, we’ll use the Baeldung University schema.

2. Sorting by a List of Strings in MySQL

Let’s sort the list of student names present in the name column of the Student table in MySQL. Also, we’ll sort the list of names residing in the name column of the Department table.

2.1. Using ORDER BY

In MySQL, we commonly use the ORDER BY clause to sort data, including strings. By default, this clause sorts in ascending order (A-Z). However, we can also specify descending order (Z-A) by using the DESC reserved keyword.

Let’s sort the name column in ascending order. To do this, we can simply use ORDER BY, and optionally add ASC, as both mean the same thing:

SELECT name 
FROM Student
ORDER BY name;

-- or
 
SELECT name 
FROM Student
ORDER BY name ASC;

After successful execution of the above query, we get the sorted list of strings:

name
--------------------
Agatha Christi
Albert Decosta
Jia Grey
John Liu
Julia Roberts
Param Mohan
Peter Liu
Phellum Luis
Philip Lose
…

Next, let’s sort the same column in descending order by using the DESC keyword with our ORDER BY clause:

SELECT name 
FROM Student
ORDER BY name DESC;

Thus, we get the list of strings in reverse alphabetical order:

name
-----------------------
Vini Puh
Vineet Jha
Vikram Kohli
Vikas Jain
Siren Lobo
Samantha Prabhu
Sam Roberts
Rose Rit
…

We can also sort all columns of a table based on a single string column. For instance, let’s see a query that sorts all columns of the Student table in descending order of the name column:

SELECT * 
FROM Student
ORDER BY name DESC;

2.2. Using ORDER BY and FIELD

In addition to the ORDER BY clause, MySQL also offers a special built-in FIELD function, which allows users to specify a custom sorting order. This function is useful when we require sorting data based on a predefined list of values.

Let’s execute a query to sort the Department table by a custom order of department names in descending order. Any names not specified in the list sorts in ascending order and appear at the end:

SELECT * 
FROM Department
ORDER BY FIELD(name, "Electronics and Communications", "Mathematics", "Civil Engineering") DESC;

Notably, we can observe that the output contains the three specified strings in decreasing order at the beginning, followed by the remaining values in the name column:

 id |  name                         | code   |
----+-------------------------------+--------+
  4 | Civil Engineering             | CE     |
  5 | Mathematics                    | MA    |
  2 | Electronics and Communications | EC    |
  1 | Computer Science              | CS     |
  3 | Mechanical Engineering        | ME     |

As we can see, the three values we listed in FIELD are first, with the rest appearing in ascending order.

3. Sorting by a List of Strings in SQL Server

SQL Server also provides the ability to sort data based on a list of strings.

Let’s take a look at two of them: The ORDER BY clause and a more complex approach with the CASE statement.

3.1. Using ORDER BY

In SQL Server, we can use the ORDER BY clause to sort data, just like in MySQL. Let’s sort the Department table by the code column in descending order:

SELECT *
FROM Department
ORDER BY code DESC;

After executing the above query, we observe that the Department table is sorted in reverse alphabetical order based on the code column:

 id | name                         | code   |
----+------------------------------+--------+
  3 | Mechanical Engineering        | ME     |
  5 | Mathematics                   | MA     |
  2 | Electronics and Communications| EC     |
  1 | Computer Science              | CS     |
  4 | Civil Engineering             | CE     |

3.2. Using ORDER BY and CASE

SQL Server also supports the CASE statement, which allows for more control over sorting by providing a custom order using conditional logic. To do so, it evaluates a list of conditions and returns a specific value when the first condition is met. However, if no condition is met, it returns an optional ELSE value or NULL by default, with all remaining values sorted accordingly.

Notably, this statement also works the same in MySQL databases.

Moving forward, let’s discuss a query to sort all rows of the Department table based on a custom order of department names:

SELECT *
FROM Department
ORDER BY 
    CASE name
        WHEN 'Electronics and Communications' THEN 1
        WHEN 'Mathematics' THEN 2
        WHEN 'Computer Science' THEN 3
        ELSE 4
    END;

We can observe in the output that the first three string values in the name column are sorted in the specified manner. However, the remaining values are sorted in ascending order by default based on the id column.

| id  | name                           | code  |
+-----+--------------------------------+-------+
| 2   | Electronics and Communications | EC    |
| 5   | Mathematics                    | MA    |
| 1   | Computer Science               | CS    |
| 3   | Mechanical Engineering         | ME    |
| 4   | Civil Engineering              | CE    |

Now, we can effortlessly sort a list of strings in the SQL Server also.

4. Sorting by a List of Strings in PostgreSQL

PostgreSQL also offers users the ability to sort strings in both alphabetical order and a custom predefined order.

4.1. Using ORDER BY

In PostgreSQL, we can use ORDER BY to sort data, just like in MySQL and SQL Server.

Let’s sort name column as we’ve already done before:

SELECT *
FROM Department
ORDER BY name DESC;

The above query returns the following result:

id |              name              | code
----+--------------------------------+------
  3 | Mechanical Engineering         | ME
  5 | Mathematics                    | MA
  2 | Electronics and Communications | EC
  1 | Computer Science               | CS
  4 | Civil Engineering              | CE

4.2. Using ORDER BY and ARRAY_POSITION

PostgreSQL also offers the ARRAY_POSITION function that enables users to define a custom sorting order for strings. This function is particularly useful when sorting data in a non-alphabetical sequence.

Let’s sort the name column in a specified manner using the ARRAY_POSITION function for three values. The rest of the values sort automatically in ascending order:

SELECT name 
FROM Department 
ORDER BY ARRAY_POSITION(ARRAY['Computer Science', 'Mathematics', 'Civil Engineering'], name);

We can observe the specified sequence in the output below:

             name
--------------------------------
 Computer Science
 Mathematics
 Civil Engineering
 Electronics and Communications
 Mechanical Engineering

5. Conclusion

In this article, we explored how to sort a list of strings in SQL across three different databases: SQL Server, MySQL, and PostgreSQL. Additionally, we discussed the functions for each database to sort strings in a custom order.