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?

  • 1
    order by name will give you different order (3,1,2) in the result than you claim (1,2,3). Mar 27 '15 at 14:14

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.

  • Thanks @ypercube! I edited that in the original question where it was wrong, and promptly did it myself too! Mar 27 '15 at 14:40
  • I also removed one of the likes in the where. Not needed. Mar 27 '15 at 14:41
  • Thanks... I can never remember if '%' means "zero or more" or "one or more". Mar 27 '15 at 14:43
  • 1
    order by name like 'ci%' desc, name will also work but isn't very intuitive. order by not name like 'ci%', name is one character shorter even -- and is completely counterintuitive. Mar 27 '15 at 14:49
  • 1
    order by -(name like 'ci%'), name will probably work, too (one more addition to your collection of counterintuitive code samples ;) Mar 27 '15 at 15:16

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


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;
  • This doesn't solve the asker's problem as the exact expression to use inside the order by clause is what's crucial here. Feb 5 '21 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.