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 |
order by substring(name, 1, 2) = 'BB' DESC should also work - is there any bad practice about this, and is case when preferred? Update: With your update it makes a lot more sense to use case. Nevermind. :-)
order by then you can order by desc but if you have a several specific orders then you can use case
This will order all rows by name. It will first put rows >= 'BB' ordered by name, and then rows < 'BB' also ordered by name, making it loop the way you want:
select *
from your_table
order by name<'BB', name
