We can use the ORDER BY clause to arrange the result set in a particular order; the Order is returned from the SELECT statement.

Syntax :

SELECT exp FROM 
TABLE [WHERE ..] ORDER BY exp [ASC|DESC];

We need to mention a column or an expression with the method.
We can use the grouping function in the ORDER BY clause, i.e., GROUP BY clause. Ordering of data is performed after grouping operation.
If you want to arrange the data in a specific order and have similar data, we can use multiple ordering expressions separated by commas.
Initially, rows will be ordered by the first condition, followed by the second one.
You can order the data in both the ways ascending and descending using ASC and DESC, respectively. By default, rows are sorted in ascending order.
Redundancy of data is eliminated in the ORDER BY clause, i.e., if there are two rows with similar data, only one will be displayed.
ORDER BY can also be used to perform other operations like DELETE and UPDATE generally with the LIMIT clause

Let’s see some examples:

We will be using the Table named ‘States’, which contains two columns – Codes and Name of States.

It looks like:

MariaDB [student]> select * from States;

Next we will use ORDER BY clause.

ORDER BY clause using one column:

MariaDB> select name from states order by name;

In the above example, we have ordered the Table using a single column ‘name’. As we are using a varchar column to sort, rows are sorted alphabetically and in ascending Order as we have not specified any ordering keyword. Hence, by default, it sorts in ascending order.

MariaDB> select code, name from states order by code;

In the above example, we are sorting the states using a code column that is an integer, so numerically, data is sorted

Sorting in Descending Order:

MariaDB> select code, name from states order by code desc;

In the above example, we have sorted the states in descending order using the column ‘code’. Here we have used the keyword ‘DESC’, which tells MariaDB to sort in descending order.

ORDER BY clause using two-column:

In the following statement, we are using the ORDER BY clause to sort states by code and name.

MariaDB [student]> select code, name from states order by code, name;

In the above example, Order by clause is sorting the data in the following Order:

First is sorts using code columns from minimum to maximum.
After that, the rows are sorted using the name column alphabetically

ORDER BY CLAUSE with NULL VALUES

In MariaDB, NULL is a special value that represents a value that is missing.

NULL is treated as the lowest value; therefore, when we use ORDER BY in null values, they appear first.

MariaDB [student]> select code, name from states order by code;

In the above example, NULL appears at the start, and the remaining values succeeding NULL values.

ORDER BY in UPDATE statement along with LIMIT clause

We can use the UPDATE ORDER BY and LIMIT simultaneously in one statement.

For example:

Before Updating:

In the above table, we can see two rows with name values as Leh and different codes. So we will update them.

MariaDB [student]> UPDATE states SET code=21 WHERE name='Leh' ORDER BY code ASC LIMIT 1;

In the above statement, we have used UPDATE to Update the row with the name ‘Leh,’ and we have used ORDER BY, which orders by code.

First, we are ordering the data by code in ascending Order then updating the row code as 21, i.e., the row with a smaller code value will be changed, and we have given LIMIT as 1, which means one row will be changed. As we have seen, there are two rows with the name value as “Leh,” so the smaller code value’s code will be changed to 21.

So here we can see the code value NULL value is changed to 21, i.e., the one which comes first when we order by code.