MySQL: how to order a result if certain records contain specific words

Question

I would like to show a query result displaying titles which contain Summer festival and Summer festivals ordered first in the list of records.

This very basic SQL query gives following result:

SELECT title FROM items


title
-----------------
Just a test
Just another test
New test
Testing
Summer festival
Summer festivals

My goal is to display the result as following:

title
-----------------
Summer festival
Summer festivals
Just a test
Just another test
New test
Testing

Take a look at this : https://stackoverflow.com/questions/18725941/mysql-order-by-best-match — Hamza Abdaoui, Aug 09 '17 at 09:12

score 0 · Accepted Answer · answered Aug 09 '17 at 09:13

0

Try conditional order by

SELECT title 
FROM items
ORDER BY CASE WHEN title LIKE '%Summer festival%' THEN 1 ELSE 2 END, title

answered Aug 09 '17 at 09:13

Serg

22,285
5
21
48

score 0 · Answer 2 · edited Aug 09 '17 at 09:19

0

To order by a specific string you could use something like this

SELECT title FROM items ORDER BY (CASE WHEN title = 'Summer festival' THEN 1 WHEN title = 'Summer Festivals' THEN 2 ELSE 3 END)

That should give you your output you require by ordering by two specific strings first then displaying the rest of the results after.

edited Aug 09 '17 at 09:19

Barmar

741,623
53
500
612

answered Aug 09 '17 at 09:14

Harvey

1,320
3
13
33

MySQL: how to order a result if certain records contain specific words

2 Answers2