How to ORDER BY last 2 character string in MySQL?

MySQLMySQLi Database

You can use ORDER BY RIGHT() to ORDER BY last 2 character string.

The syntax is as follows

select yourColumnName from yourTableName ORDER BY RIGHT(yourColumnName , 2);

To understand the above syntax, let us create a table. The query to create a table is as follows

mysql> create table OrderByLast2CharactersDemo
   -> (
   -> CustomerId varchar(20),
   -> CustomerName varchar(20)
   -> );
Query OK, 0 rows affected (0.58 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('John-98','John');
Query OK, 1 row affected (0.20 sec)
mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('Carol-91','Carol');
Query OK, 1 row affected (0.21 sec)
mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('Bob-99','Bob');
Query OK, 1 row affected (0.22 sec)
mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('David-67','David');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from OrderByLast2CharactersDemo;

The following is the output

+------------+--------------+
| CustomerId | CustomerName |
+------------+--------------+
| John-98    | John         |
| Carol-91   | Carol        |
| Bob-99     | Bob          |
| David-67   | David        |
+------------+--------------+
4 rows in set (0.00 sec)

Here is the query to order by last 2 characters string.

Case 1: The result is in ascending order.

The query is as follows −

mysql> select CustomerId from OrderByLast2CharactersDemo ORDER BY RIGHT(CustomerId , 2);

The following is the output

+------------+
| CustomerId |
+------------+
| David-67   |
| Carol-91   |
| John-98    |
| Bob-99     |
+------------+
4 rows in set (0.01 sec)

Case 2 The result is in descending order.

The query is as follows −

mysql> select CustomerId from OrderByLast2CharactersDemo ORDER BY RIGHT(CustomerId , 2) DESC;

The following is the output

+------------+
| CustomerId |
+------------+
| Bob-99     |
| John-98    |
| Carol-91   |
| David-67   |
+------------+
4 rows in set (0.00 sec)
raja
Published on 01-Apr-2019 15:07:40
Advertisements