Natural Sorting in MySQL – A Simple Guide

By /
Natural Sorting In MySQL

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.

Also read: MySQL ORDER BY Clause – How to Sort Records In A Table?

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)
);Code language: SQL (Structured Query Language) (sql)

Output-

Create Table Sorting
Create Table Sorting
Insert into object(id)
values ('1'),('2'),('2B'),('15F'),('22Z');Code language: SQL (Structured Query Language) (sql)

Output-

Insert Values Sorting
Insert Values Sorting

ii) Now query the data from the table.

Select id
From object
Order By id;Code language: SQL (Structured Query Language) (sql)

Output-

Query Sorting
Query Sorting

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
22ZCode language: SQL (Structured Query Language) (sql)

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)
);Code language: SQL (Structured Query Language) (sql)
Insert into object(prefix, suffix)
values ('1','C'),('2','A'), ('1','Z'), ('16','T');Code language: SQL (Structured Query Language) (sql)

Output –

Prefix Suffix
Prefix Suffix

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;Code language: SQL (Structured Query Language) (sql)

Output-

Type Cast Sorting
Type Cast Sorting

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');Code language: SQL (Structured Query Language) (sql)

Now query the data –

Select num
From data
Order By Length(num), num;Code language: SQL (Structured Query Language) (sql)

Output –

Length Sorting
Length Sorting

So as we can see the data is sorted in an appropriate way with the help of Length() function.

Conclusion

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.