mysql - order by first condition in where clause

Question

I have a table consists of the following fields

id, name

with the following values

1, ciro
2, test ciro
3, ciprox
4, other
5, other

i would like to get all the values that begin with "ci" or contain "ci" but show me the results before they start with ci and then the rest.

the query

select * FROM table WHERE name like 'ci%' or name like '%ci%' order by name;

I returns

1, ciro
2, test ciro
3, ciprox

I want

1, ciro
3, ciprox
2, test ciro

therefore before the rows in which name begins with "ci" and then those that contain

is there a way without using two queries to obtains the result?

Answer 1

This is quite a common problem! A case expression helps here. A query something like (untested)

select *
from table
where name like '%ci%'
order by case when name like 'ci%' then 0 else 1 end, name;

should do what you want.

Answer 2

You should go for parent-child-query. The child or inner query will return the sorted set and In outer query just put your condition:

select * from (
  select * from `table_name` order by attribute asc) as sample_table_name 
where attribute >=x;

Answer 3

You should use ORDER BY CASE to sort the items in a way that suits your specific purpose.

See here and here for examples on the usage.

More CASE operator info here