How to ORDER BY RELEVANCE in MySQL?

To order by relevance, use the case statement. To understand the concept, let us create a table. The query to create a table is as follows −

mysql> create table OrderByRelevance
   -> (
   -> UserId int,
   -> UserName varchar(200)
   -> );
Query OK, 0 rows affected (0.51 sec)

Now you can insert some records in the table using insert command. The query is as follows −

mysql> insert into OrderByRelevance values(101,'Carol Smith');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByRelevance values(102,'Carol Adams');
Query OK, 1 row affected (0.17 sec)

mysql> insert into OrderByRelevance values(103,'Carolnathan Todd');
Query OK, 1 row affected (0.33 sec)

mysql> insert into OrderByRelevance values(104,'John Smith');
Query OK, 1 row affected (0.22 sec)

mysql> insert into OrderByRelevance values(105,'Sam Carol Bond');
Query OK, 1 row affected (0.12 sec)

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

mysql> select *from OrderByRelevance;

Output

+--------+------------------+
| UserId | UserName         |
+--------+------------------+
| 101    | Carol Smith      |
| 102    | Carol Adams      |
| 103    | Carolnathan Todd |
| 104    | John Smith       |
| 105    | Sam Carol Bond   |
+--------+------------------+
5 rows in set (0.00 sec)

Here is the query to order by relevance. The query is as follows −

mysql> select max(UserId)as Id,UserName from OrderByRelevance
   -> where UserName like '%Carol%' group by UserName
   -> order by case when UserName like 'Carol%' THEN 0
   -> WHEN UserName like '% %Carol% %' THEN 1
   -> WHEN UserName like '%Carol' THEN 2
   -> else 3
   -> end,UserName;

Output

+------+------------------+
|   Id | UserName         |
+------+------------------+
| 102  | Carol Adams      |
| 101  | Carol Smith      |
| 103  | Carolnathan Todd |
| 105  | Sam Carol Bond   |
+------+------------------+
4 rows in set (0.00 sec)