Mysql Order By specific string

Question

I can't seem to find an answer to this one:

Say I have a table like so:

ID   Name
------------------------
1    AAAAAAAAA
2    ABAAAAAAA
3    BBAAAAAAA
4    CDAAAAAAA
5    BBAAAAAAA

Is there a way I can order by name - but - start that order from say BB and let it loop back round (instead of from A-Z, go from BB to BA)

The final result would then be:

3    BBAAAAAAA
5    BBAAAAAAA
4    CDAAAAAAA
1    AAAAAAAAA
2    ABAAAAAAA

Does that make sense?

Answer 1

If you want the BB to appear at the beginning you can use:

select *
from yourtable
order by case when substring(name, 1, 2) = 'BB' then 0 else 1 end

See SQL Fiddle with Demo

If you want CD to appear second, then use:

select *
from yourtable
order by 
  case 
    when substring(name, 1, 2) = 'BB' then 0 
    when substring(name, 1, 2) = 'CD' then 1 
    else 2 end, name

See SQL Fiddle with Demo

Result for second query:

| ID |      NAME |
------------------
|  3 | BBAAAAAAA |
|  5 | BBAAAAAAA |
|  4 | CDAAAAAAA |
|  1 | AAAAAAAAA |
|  2 | ABAAAAAAA |