CODEWITHSUNDEEP
mysqlsql
amitchhajer
amitchhajer

Reputation: 12830

mysql search weight to result

Database entries

table test

 name
========================
 Sunae Kasimov
 Obsop Natty
 Preem Kuman

Mysql query 

select * 
from test 
where name like 'na%' or 
      name like '% na%' or 
      name like '%na%';

return in order 1 and then 2 but i wanted in order 2 and 1. That is want to give more weight to '% na%' then '%na%'

Any way this can be done in mysql?

Upvotes: 3

Views: 1064

Answers (2)

valex
valex

Reputation: 24144

You should create virtual filed in your select and order by it. For example:

select test.*,
case when name like '% na%'
         then 1
     else 2
end as OrderField
from test 
where name like 'na%' or 
      name like '% na%' or 
      name like '%na%'
order by OrderField

Upvotes: 2

AnandPhadke
AnandPhadke

Reputation: 13506

select * from 
(
select * from name where name like '% na%'
union
select * from name where name like 'na%'
union
select * from name where name like '%na%'
) a

Upvotes: 2

Related Questions