In this tutorial, we will study natural sorting in MySQL. The ORDER BY clause is used in MySQL for sorting the data objects in MySQL.
Introduction to Natural Sorting
While MySQL does not offer any natural sorting methods by default, we can use the ORDER BY Clause for the same. ORDER BY is used to sort the data or records of the result in ascending or descending order. By default, the sorting order of ORDER BY clause is in ascending order. For descending order, specify using DESC keyword.
Implementing Natural Sorting in MySQL
- First, we will create a sample table and insert data into it.
- Second, query the data from the table and check whether it is sorted in the way we expected or not.
i) Create a table named object and then insert data into it.
Create Table object ( num int, id varchar(200) );
Insert into object(id) values ('1'),('2'),('2B'),('15F'),('22Z');
ii) Now query the data from the table.
Select id From object Order By id;
Here, we see that the result set is not sorted in the way we expected. The expected sorted data set will be as follows-
1 2 2B 15F 22Z
Now to sort the data in the above way, ORDER BY clause will be used. MySQL does not give any inbuilt function to sort the data and hence, ORDER BY is used.
A) Dividing the Entity Into Two Parts
Here, we will divide the entity into two parts namely prefix and suffix, and then concatenate them. The prefix part will store and sort the numeric part of data and suffix will store and sort the alphabet part of data.
The code for it is-
Create Table onz ( prefix int, suffix varchar(200) );
Insert into object(prefix, suffix) values ('1','C'),('2','A'), ('1','Z'), ('16','T');
But this solution is not that feasible because every time we need to divide the entity into two columns which can sometimes be very time taking.
B) By Using Type Casting
In this first of all, the data which is to be sorted will be converted into an unsigned integer, and then using the ORDER BY clause the data will be sorted numerically first and then alphabetically.
The code for the following is-
SELECT id FROM object Order By Cast(id as Unsigned) , id;
In this way, the data is sorted both numerically and alphabetically.
C) Using LENGTH() Function
Sometimes we have data in the form of B-2, A-1, etc. and to sort this type we need to use the Length() function.
Basically, the Length() function finds the length of the string and then sorts the data length-wise first and then next column-wise.
Let’s create another table named data and insert values into it.
Create Table data ( num varchar(300) ); Insert into data(num) Values ('B-1'), ('A-5'), ('A-2'), ('C-3'), ('B-3');
Now query the data –
Select num From data Order By Length(num), num;
So as we can see the data is sorted in an appropriate way with the help of Length() function.
In this tutorial, we studied the sorting of data in MySQL by using the Order By clause and also using different techniques.
For additional references, see the official documentation of MySQL.