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?
解决方案
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
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
Result for second query:
| ID | NAME |
------------------
| 3 | BBAAAAAAA |
| 5 | BBAAAAAAA |
| 4 | CDAAAAAAA |
| 1 | AAAAAAAAA |
| 2 | ABAAAAAAA |