Mysql: Order by like?

 SELECT * 
 from
 (
  SELECT u.*, 1 OrderNum 
  FROM users 
  WHERE (name LIKE "%John%")
  UNION 
  SELECT u.*, 2 OrderNum 
  FROM users 
  WHERE (name LIKE "%Doe%")
  )
  Order by OrderNum

Read up on Boolean Fulltext Searches, with which you can do ordering.

order by case 
    when name LIKE "%John%" then 1 
    when name LIKE "%Doe%"  then 2 
    else 3 
end

To build on RedFilter's answer, you could make the rows that have both keywords to be at the top:

order by case 
when (name LIKE "%John%" and name LIKE "%Doe%") then 1 
when name LIKE "%John%" then 2
when name LIKE "%Doe%"  then 3
end

Tags:

Mysql

Sql

Sql Order By

Sql Like

Related