In MySQL, the ORDER BY clause is used to sort the records fetched by the SELECT statement, in ascending or descending order. And while the usage of this command is simple in basic cases, there are more complex scenarios requiring much more attention.
This article will explore the essence, the syntax, and the usage scenarios for MySQL ORDER BY, and illustrate them with real-life examples. So, let’s start.
Contents
- What does ORDER BY do in MySQL?
- ORDER BY syntax in MySQL
- A simple example of ORDER BY
- ORDER BY with ASC or DESC attributes
- ORDER BY with multiple columns
- ORDER BY ASC and DESC in the same query
- ORDER BY random function
- ORDER BY date
- ORDER BY and LIMIT
- ORDER BY and NULL
- Case insensitive ORDER BY
- ORDER BY length of characters
- ORDER BY and arithmetic operators
- ORDER BY using a custom list
- ORDER BY with GROUP BY and aggregate functions
- How dbForge Studio for MySQL helps sort data?
- How to use visual MySQL Query Builder
- Summary
What does ORDER BY do in MySQL?
The most common database operation is retrieving data. For that, we use the SELECT statement which is the most popular command for many database specialists. The catch is, SELECT fetches records and presents them to the user unsorted. The data looks like a mess, and it is almost unusable until you sort it.
The ORDER BY clause comes in extremely handy in this case. It sorts the retrieved records and streamlines them. Thus, the query brings us the data arranged at once. We can start working with it efficiently without wasting time on postprocessing.
The MySQL ORDER BY clause is frequent in the SELECT queries where it performs all kinds of sorting tasks: from simple to more complex scenarios. But before we start reviewing these scenarios, let us explore the ORDER BY syntax in MySQL – where and how to use this clause in queries correctly.
ORDER BY syntax in MySQL
The basic syntax of the SELECT query with the ORDER BY clause is:
SELECT
column1, column2...columnN
FROM
table_name
ORDER BY
expression [ASC|DESC];In the ORDER BY syntax, MySQL uses the following parameters:
- column1, column2… – the names of the columns to retrieve the data from (it can be one or multiple columns).
- table_name – the name of the table containing the data we need.
- expression – a column or another value, like the date, function, etc. used to sort the data.
- ASC – an optional parameter enforcing the records sorting in ascending order. It is set by default: MySQL ORDER BY ASC is the same as simple ORDER BY.
- DESC – an optional parameter that enforces the records sorting in descending order.
This ORDER BY MySQL query syntax works as follows:
FROM (where to get the data) > SELECT (which data to fetch) > ORDER BY (how to arrange the results)
Next, we’ll explore the practical usage of ORDER BY in statements. We use the Sakila sample database and the popular MySQL IDE – dbForge Studio for MySQL by Devart. It is a universal IDE for database management, development, and administration. Among the other many advantages, the Studio helps us construct queries in a wink and make sure they are correct at once.
A simple example of ORDER BY
We’ll start with the simplest task to sort the clients’ data by the first names, using the MySQL ORDER BY alphabetical sorting of the results.
SELECT
customer_id,
first_name,
last_name,
email,
active,
create_date,
last_update
FROM sakila.customer
ORDER BY first_name;
ORDER BY with ASC or DESC attributes
By applying the ASC and DESC parameters, we can vary the order of sorting:
SELECT
customer_id,
first_name,
last_name,
email,
active,
create_date,
last_update
FROM sakila.customer
ORDER BY first_name ASC;Note that the order of results is the same:
Now, we’ll need the MySQL ORDER BY DESC modifier. We can use this method for numeric values and dates.
SELECT
customer_id,
first_name,
last_name,
email,
active,
create_date,
last_update
FROM sakila.customer
ORDER BY customer_id DESC;
It is possible to get the reversed results without changing ASC to DESC. For that, use “-” in the expression. ORDER BY customer_id DESC is equivalent to ORDER BY -customer_id ASC, and they deliver the same results:
SELECT
customer_id,
first_name,
last_name,
email,
active,
create_date,
last_update
FROM sakila.customer
ORDER BY -customer_id ASC;
As you can see, the results are the same. This simple tip may help you if you, for example, need the results sorted in descending order, but for some reason don’t want to use MySQL ORDER BY DESC (and vice versa). Or, you just want the results sorted in both ways with minimum changes in the initial code.
ORDER BY with multiple columns example
In the previous examples, we used the ORDER BY field MySQL sorting by one field only. However, in many cases, we need the results sorted by several columns at once.
The query syntax will then include the list of columns separated by commas in ORDER BY:
ORDER BY
column1,
column2;We want to sort results using the “MySQL ORDER BY two columns” scenario. The system will sort the results by column1, then by column2, all in ascending order.
We can have as many columns as needed, and the MySQL ORDER BY two columns’ priority will be set in the same way.
Let’s see how it works in a real-life example. To check our case of the MySQL ORDER BY two columns combined, we’ll use two tables – payment and customer from the Sakila database.
SELECT
c.first_name,
c.last_name,
p.amount,
p.payment_date,
p.last_update
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
ORDER BY p.amount, c.first_name;
We can sort the result by the sequence number instead of the column name (column 1 will be 1, column 2 will be 2, etc.). In this case, the previous query with ORDER BY multiple columns MySQL scenario can be as follows:
SELECT
c.first_name,
c.last_name,
p.amount,
p.payment_date,
p.last_update
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
ORDER BY 3, 1;You can see that p.amount is the 3rd one in the sequence, while c.first_name takes the 1st position:
ORDER BY ASC and DESC in the same query
The scenario where we want to sort the data from several columns by several columns can be more complex. We may need these columns sorted in different orders. To achieve that, we may have the MySQL ORDER BY ASC and DESC in the same query and apply both modifiers simultaneously.
The basic syntax looks as below:
ORDER BY
column1 ASC,
column2 DESC;Our example will demonstrate sorting the data in different columns in both ascending and descending order:
SELECT
c.first_name,
c.last_name,
p.amount,
p.payment_date,
p.last_update
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
ORDER BY p.amount DESC, c.first_name ASC;
As in the previous example, we can use the sequence numbers instead of the column names:
SELECT
c.first_name,
c.last_name,
p.amount,
p.payment_date,
p.last_update
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
ORDER BY 3 DESC, 1 ASC;
ORDER BY random
This scenario is more sophisticated. The MySQL ORDER BY Random option is the specific method of data sorting where we query the data randomly from some table. A typical example is fetching some random blog posts, quotes, images from the gallery, etc., and placing them on some web resource as a demonstration.
If we use MySQL ORDER BY Rand in the SELECT statement, it works in the following way:
- The RAND() function generates a random value for each table row;
- The ORDER BY clause sorts the results in ascending or descending order by those random numbers generated and assigned to fields by the RAND() function.
The following example shows the case of using MySQL ORDER BY RAND() to retrieve records from the customer table in random order. The result will be different for every script execution.
SELECT
customer_id,
first_name,
last_name,
email,
active,
create_date,
last_update
FROM sakila.customer
ORDER BY RAND();
However, it is important to note that this method might not be suitable for large tables. MySQL will have to process the entire table to assign random values, which takes time and consumes resources. In smaller tables, it is more efficient.
ORDER BY date
Sorting data by datetime is one of the most common scenarios. The below examples demonstrate how to sort the records using MySQL ORDER BY date. Pay attention that the results brought by the MySQL ORDER BY date and time will differ:
SELECT
c.first_name,
c.last_name,
p.amount,
p.payment_date,
p.last_update
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
ORDER BY payment_date, p.amount DESC, c.first_name;
SELECT
c.first_name,
c.last_name,
p.amount,
p.payment_date,
p.last_update
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
ORDER BY DATE(payment_date), p.amount DESC, c.first_name;
The MySQL ORDER BY datetime option demonstrates how the presence of both date and time impacts the results.
ORDER BY and LIMIT
If we need to limit ORDER BY MySQL results, we use the LIMIT clause in the query. In this case, the ORDER BY clause will first sort the results, and then the LIMIT clause will restrict the number of records shown in the query output.
Pay attention that the results will vary depending on the ASC or DESC modifier used (the rest of the query remains unchanged):
SELECT
customer_id,
first_name,
last_name,
email,
active,
create_date,
last_update
FROM sakila.customer
ORDER BY first_name ASC
LIMIT 15;
SELECT
customer_id,
first_name,
last_name,
email,
active,
create_date,
last_update
FROM sakila.customer
ORDER BY first_name DESC
LIMIT 15;
It is worth mentioning the combination of ORDER BY, RAND(), and LIMIT.
MySQL ORDER BY RAND() arranges the results according to random values that the RAND() function assigned to the rows. Then, the LIMIT clause selects the first row of the results table and fetches the number of results specified in the query.
ORDER BY and NULL
Quite often, records we fetch with the SELECT statement contain the NULL values. The MySQL ORDER BY NULL method can sort the data containing these values.
MySQL considers NULLs lower than any non-NULLs. Thus, if we want to force MySQL ORDER BY show NULLs first in the result set, we need to sort the records in ascending order:
SELECT
a.address_id,
a.address,
a.address2,
a.district,
a.city_id,
a.postal_code,
a.phone,
a.location,
a.last_update
FROM sakila.address a
ORDER BY address2 ASC;
Consequently, ORDER BY NULL puts NULLs last in descending order:
SELECT
a.address_id,
a.address,
a.address2,
a.district,
a.city_id,
a.postal_code,
a.phone,
a.location,
a.last_update
FROM sakila.address a
ORDER BY address2 DESC;
Note that NULL and – (empty line) are different values in MySQL. If your table contains both these values, you might want to exclude the NULLs’ effect. The IFNULL() function lets us resolve the issue:
SELECT
a.address_id,
a.address,
a.address2,
a.district,
a.city_id,
a.postal_code,
a.phone,
a.location,
a.last_update,
IFNULL(address2, 'a')
FROM sakila.address a
ORDER BY IFNULL(address2, 'a') ASC;
Case-insensitive ORDER BY
In MySQL, values in ORDER BY are case-sensitive. Thus, if we have the same values in the rows, but in the upper or low case, MySQL will consider them to be different values. It may affect sorting.
To avoid the problem, we can use several methods, such as collation or modifying the query in such a way that it will switch all retrieved entries into upper or lower case.
Have a look at the below example: here we add the UPPER modifier to the query to switch all values to the upper case:
SELECT
c.customer_id,
c.store_id,
c.first_name,
c.last_name,
c.email,
c.address_id,
c.active,
c.create_date,
c.last_update
FROM customer c
ORDER BY UPPER(first_name);
And this example demonstrates the usage of the collate option:
SELECT
c.customer_id,
c.store_id,
c.first_name,
c.last_name,
c.email,
c.address_id,
c.active,
c.create_date,
c.last_update
FROM customer c
ORDER BY first_name COLLATE `utf8mb3_unicode_ci`;
These methods are equally efficient to make the ORDER BY task case insensitive.
ORDER BY length of characters
Sorting by the length of the result string is another common task. There is an effective method to do the job using the ORDER BY LENGTH of string option. It adds the LENGTH() function to the clause and sets the query to sort the results in ascending or descending order by length.
SELECT
city_id,
city,
country_id,
last_update
FROM city
ORDER BY LENGTH(city) ASC;
ORDER BY and arithmetic operators
One more scenario suggests using the arithmetic operator in the ORDER BY clause. In this case, we apply some arithmetic operators to the data we fetch. Then ORDER BY sorts the values obtained as the result of those operators’ work:
SELECT f.film_id,
f.title,
f.description,
f.release_year,
f.language_id,
f.original_language_id,
f.rental_duration,
f.rental_rate,
f.length,
f.replacement_cost,
f.rating,
f.special_features,
f.last_update
FROM sakila.film f
ORDER BY f.replacement_cost * f.rental_rate * f.length;
ORDER BY using a custom list
Custom lists containing text values or texts with numbers are very helpful when you need to sort values that aren’t suitable for sorting alphabetically.
Have a look at the example below – we need to sort movies according to their MPAA ratings:
SELECT f.film_id,
f.title,
f.description,
f.release_year,
f.language_id,
f.original_language_id,
f.rental_duration,
f.rental_rate,
f.length,
f.replacement_cost,
f.rating,
f.special_features,
f.last_update
FROM sakila.film f
ORDER BY FIELD(rating, 'G', 'NC-17', 'PG', 'PG-13', 'R');
ORDER BY with GROUP BY and aggregate functions
Both ORDER BY and GROUP BY will sort the results brought by the SELECT query. However, they do it differently. While ORDER BY sorts the records by one or multiple columns, GROUP BY arranges the data into groups using aggregate functions (COUNT(), MAX(), MIN(), SUM(), and AVG()).
In the query, GROUP BY will apply the aggregate function to process the records and group them as summaries for similar values. Then, ORDER BY sorts these groups in ascending or descending order.
SELECT
c.first_name,
c.last_name,
SUM(p.amount),
p.payment_date
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
GROUP BY c.first_name,c.last_name, p.payment_date
ORDER BY date(p.payment_date), SUM(p.amount) ASC;
SELECT
c.first_name,
c.last_name,
SUM(p.amount),
p.payment_date
FROM sakila.payment p
INNER JOIN customer c
ON p.customer_id = c.customer_id
GROUP BY c.first_name,c.last_name, p.payment_date
ORDER BY date(p.payment_date), SUM(p.amount) DESC;
These are the most common scenarios of using MySQL ORDER BY in queries.
How dbForge Studio for MySQL helps sort data?
When you are working with dbForge Studio for MySQL, you can sort the data visually without changing the query.
This option is simple but helpful, as it saves you time – there is no need to edit the query (even though it is a quick and easy task in the Studio). Still, you need to spend time altering the syntax and then getting back to the original. With many queries, it becomes tiresome and annoying. So, changing the order of results with one click is preferable.
How to use visual MySQL Query Builder functionality
As you could see from the examples presented in this article, SQL queries can be quite complex. Writing them correctly with proper syntax and all keywords and values in the right order is a task requiring knowledge and precision.
dbForge Studio for MySQL allows you to save time and avoid errors using the visual MySQL Query Builder feature. With its help, you can create queries and subqueries, building them from visual blocks on a diagram and applying JOINs with one click. Just have a look at the below video that illustrates the work of this tool.
And if you prefer a more traditional approach to query writing, the Studio for MySQL presents plenty of helpful features that help you write the code much faster, beautify it effectively, and achieve 100% correctness.
Summary
Data has little value without processing, and sorting the data is one of the fundamental needs for any database user. Fortunately, it is not a problem – there are standard methods that allow for performing such basic tasks easily.
In this article, we have explored the most common work scenarios where the user might need to order the results, simply, or with more complex parameters.
dbForge Studio for MySQL we used to illustrate the tasks helped us significantly – it let us compose the necessary queries easily, execute them, and get the results at once in a GUI. The 30-day Free Trial with all the functionality of the Studio is available to every user.
