MySQL ORDER BY keyword match

Issue

I have a table like this:

mysql> select * from test;
+-------------+
| name        |
+-------------+
| one         |
| two         |
| three       |
| tic tac toe |
| tac toe tic |
+-------------+
5 rows in set (0.00 sec)

I would like to query it so that I get all the rows but with those rows matching a certain keyword first. This is what I got so far:

mysql> select * from test order by instr(name, 'tac') desc;
+-------------+
| name        |
+-------------+
| tic tac toe |
| tac toe tic |
| one         |
| two         |
| three       |
+-------------+
5 rows in set (0.01 sec)

The only problem with this is I would prefer to order the matching rows by how close to the beginning of the field the keyword occurs. Since instr() returns 0 for no match, non-matching rows come up first when I ORDER BY INSTR(name, ‘tac’) ASC. I haven’t been able to figure out a way around this.

I need MySQL to order like this

1
2
3
4
0
0
0

or I need instr() to return NULL instead of 0.

Solution

You need to order by 2 columns, the first one to indicate whether a match was made (to make 0s go to bottom)

select *
from test
order by
    CASE WHEN instr(name, 'tac') = 0 then 1 else 0 end,
    instr(name, 'tac') desc;

A note on using NULL, they come to the top of the query, so it won’t work for you to convert 0 to null.

select a
from (select 1 as a union all select null) b
order by a

Result

(NULL)
1

Answered By – RichardTheKiwi

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published