Mohit Bumb Mohit Bumb - 5 months ago 45
MySQL Question

How to use MySQL like with order by exact match first

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

Answer

You can do:

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