Sorty by best results

Question

Have mysql table with field 'descript' and these sample values.

malik
ali
dali
other ali

Have mysql query like:

select * from table where descript like '%ali%'

Is it possible to sort select results buy the most relevant? So in this example I would like to have it: 1. ali 2. other ali 3. dali / malik ...

You need a [full text index](http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html) on the `descript` column. Optionally, if you want first-rate text relevancy searching I'd recommend a tool specifically built for the job, like [elasticsearch](https://www.elastic.co/products/elasticsearch) — adamb, Jun 11 '16 at 14:48
Possible duplicate of [MySQL order by relevance](http://stackoverflow.com/questions/15026244/mysql-order-by-relevance) — Cobra_Fast, Jun 11 '16 at 16:24

score 1 · Accepted Answer · answered Jun 11 '16 at 15:55

You could use a CASE expression to calculate relevance for different types of matches, and order by that.

ORDER BY CASE
    WHEN descript = 'ali' THEN 1 -- exact match
    WHEN descript REGEXP '[[:<:]]ali[[:>:]]' THEN 2 -- whole word
    ELSE 3 -- anything else
END ASC

DEMO

score 0 · Answer 2 · answered Jun 11 '16 at 14:58

0

You could use locate for retrive the position oof the first occurence of the string and use like nest result

select * from table where descript like '%ali%'
order by locate('ali', descript) asc

answered Jun 11 '16 at 14:58

ScaisEdge

131,976
10
91
107

That will show `dali` and `malik` before `other ali`. – Barmar Jun 11 '16 at 15:52

Sorty by best results

2 Answers2