Mysql: Order by like?

Question

assume that we are performing search using keywords: keyword1, keyword2, keyword3

there are records in database with column "name":

1: John Doe
2: Samuel Doe
3: John Smith
4: Anna Smith

now Query:

SELECT * FROM users WHERE (name LIKE "%John%" OR name LIKE "%Doe%")

it will select records: 1,2,3 (in this order) but i want to order it by keyword in example keyword1=John, keyword2=Doe so it should be listed by keywords: 1,3,2 (because i want to perform search for "Doe" after searching for "John")

I was thinking about SELECT DISTINCT FROM (...... UNION .....) but it will be much easier to order it somehow in another way (real query is really long)

are there any tricks to create such order?

score 71 · Accepted Answer · answered Aug 31 '10 at 12:50

71

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

answered Aug 31 '10 at 12:50

D'Arcy Rittich

167,292
40
290
283

one more thing - if I understand it properly every LIKE will be executed 2 times in whole query? – dfens Aug 31 '10 at 13:05
@dfens: I am guessing that the `LIKE` in the `ORDER BY` clause will execute only on the data that was matched by the `WHERE` clause, so should be quicker. – D'Arcy Rittich Aug 31 '10 at 13:55

score 6 · Answer 2 · answered Aug 31 '10 at 14:09

6

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

answered Aug 31 '10 at 14:09

Juan Tarquino

967
7
13

score 3 · Answer 3 · answered Aug 31 '10 at 12:51

3

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

answered Aug 31 '10 at 12:51

fredley

32,953
42
145
236

score 2 · Answer 4 · answered Aug 31 '10 at 12:51

2

 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

answered Aug 31 '10 at 12:51

Michael Pakhantsov

24,855
6
60
59

score -1 · Answer 5 · answered Dec 18 '15 at 16:24

My example will Order all of the John's Alphabetically followed by the Doe's.

ORDER BY CASE
    WHEN name LIKE "John%Doe" THEN CONCAT('a',name)
    WHEN name LIKE "John%"    THEN CONCAT('b',name)
    WHEN name LIKE "%Doe"     THEN CONCAT('c',name)
    ELSE name  
END

Mysql: Order by like?

5 Answers5

Linked