MariaDB: ORDER BY Clause

This MariaDB tutorial explains how to use the MariaDB ORDER BY clause with syntax and examples.

Description

The MariaDB ORDER BY clause is used to sort the records in your result set.

Syntax

The syntax for the ORDER BY clause in MariaDB is:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ASC

Optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).

DESC

Optional. It sorts the result set in descending order by expression.

Note

• If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order. This is equivalent to ORDER BY expression ASC.

Example - Sorting without using ASC/DESC attribute

In MariaDB, you can use the ORDER BY clause without specifying the ASC or DESC modifier. When omitted, the sort order defaults to ASC, which is ascending order.

Let's take a look at how to use the ORDER BY clause without the ASC or DESC modifier.For example:

SELECT site_id, site_name
FROM sites
WHERE site_name = 'AODBA.com'
ORDER BY site_id;

This MariaDB ORDER BY example would return the site_id and site_name fields from the sites table. The results would be sorted in ascending order by the site_id. This example would be equivalent to the following ORDER BY clause:

SELECT site_id, site_name
FROM sites
WHERE site_name = 'AODBA.com'
ORDER BY site_id ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

Example - Sorting in descending order

Next, let's take a look at how to sort in descending order using the ORDER BY clause in MariaDB.

For example:

SELECT site_name, server_name
FROM sites
WHERE site_name = 'AODBA.com'
ORDER BY server_name DESC;

This ORDER BY example would return the site_name and server_name columns from the sites table. The results would be sorted in descending order by server_name.

Example - Sorting by relative position

You can also use the MariaDB ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The second field in the result set is 2, and so on.

For example:

SELECT site_id, site_name
FROM sites
WHERE site_name = 'AODBA.com'
ORDER BY 2 DESC;

This MariaDB ORDER BY would return the site_id and site_name fields from the sites table. The result set would be sorted by the site_name in descending order, since the site_name field is in position #2 in the result set. It would be equivalent to the following ORDER BY clause:

SELECT site_id, site_name
FROM sites
WHERE site_name = 'AODBA.com'
ORDER BY site_name DESC;

Example - Using both ASC and DESC attributes

When sorting your results in MariaDB, you are limited to only sorting in ascending order or descending order, you use a combination of the ASC and DESC attributes in your query.

For example:

SELECT site_name, server_name
FROM sites
WHERE site_id  75
ORDER BY site_name DESC, server_name ASC;

This ORDER BY example would return the site_name and server_name columns from the sites table. The results would be sorted by the site_name field in descending order, with a secondary sort by server_name in ascending order.