mysql - order by first condition in where clause

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?

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.

StackExchange Database Administrators Q#96446, answer score: 13