How to use MySQL like with order by exact match first

Question

I'm using MySQL 5.5 so that's why I can't use FULLTEXT search so please don't suggest it.

What I wanted to do is if user I have 5 records for example :

Amitesh
Ami
Amit
Abhi
Arun

and if someone searches for Ami then it should returns Ami first as exact match then Amit & Amitesh

what if someone search for 'mi' only in that case it should also return 3 records without any order — Mohit Bumb, Jul 09 '15 at 11:14
What about MySQL 5.5 doesn't allow FULLTEXT search? (https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html) — Gordon Linoff, Jul 09 '15 at 11:23

score 34 · Accepted Answer · edited Jun 22 '16 at 10:10

34

You can do:

select *
from table t
where col like '%Ami%'
order by (col = 'Ami') desc, length(col);

edited Jun 22 '16 at 10:10

Railslide

answered Jul 09 '15 at 11:24

Gordon Linoff

can you please explain what length(col) is used in query ? – Mohit Bumb Jul 09 '15 at 11:27
1

Probably to return 'Amit' before 'Amitech' – Vatev Jul 09 '15 at 11:36
2

If I want to match 2 column then how can I write length(col) ? – Govaadiyo May 06 '19 at 09:04

score 5 · Answer 2 · edited Jan 03 '18 at 08:21

5

SELECT *
FROM table t
WHERE col LIKE '%Ami%'
ORDER BY INSTR(col,'Ami') asc, col asc;

edited Jan 03 '18 at 08:21

Brendan

answered Jan 03 '18 at 06:21

srinivas mamidala

Should be a _DESC_ on the first **Order By** to make it work as OP wanted: `ORDER BY INSTR(col, 'Ami') DESC, col ASC`. That said, thanks! This works as a charm in Doctrine/Symfony (with Doctrine extensions enabled) while the selected answer breaks because of the `=` sign – Erdal G. Nov 13 '20 at 13:41

score 1 · Answer 3 · answered Mar 10 '21 at 10:13

1

More accurate output can be found using like in order by.

SELECT * FROM `table_name` WHERE col LIKE '%ami%' order by (col LIKE 'ami%') desc;

answered Mar 10 '21 at 10:13

3 Answers3