Mysql: Order by like?

mysql sql sql-order-by sql-like
dfens picture dfens · Aug 31, 2010 · Viewed 31.5k times · Source

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?

Answer

D'Arcy Rittich picture D'Arcy Rittich · Aug 31, 2010 
order by case 
    when name LIKE "%John%" then 1 
    when name LIKE "%Doe%"  then 2 
    else 3 
end

Related questions

How does MySQL process ORDER BY and LIMIT in a query?

I have a query that looks like this: SELECT article FROM table1 ORDER BY publish_date LIMIT 20 How does ORDER BY work? Will it order all records, then get the first 20, or will it get 20 records and order them by …

Read More
MySQL "Group By" and "Order By"

I want to be able to select a bunch of rows from a table of e-mails and group them by the from sender. My query looks like this: SELECT `timestamp`, `fromEmail`, `subject` FROM `incomingEmails` GROUP BY LOWER(`fromEmail`) ORDER BY `…

Read More
SQL order string as number

I have numbers saved as VARCHAR to a MySQL database. I can not make them INT due to some other depending circumstances. It is taking them as character not as number while sorting. In database I have 1 2 3 4 5 6 7 8 9 10... On my page …

Read More