I'm sure that title is very confusing. I can't think of a better way to put it though.
Here is my dilemma; I have a table, consisting of hundreds of rows. The current columns are id (primary key), mfgr (manufacturer number), description, price. Now because of the way the manufacturer id is, 87-10 comes before 87-11, but if I order by mfgr it will put 87-100 between those two. So I have to order by something else (in this case, the primary key auto increment ID) The problem is, on initial setup, the ID 1 will be assigned to 87-10 and the id 2 will be assigned to 87-20. If, at some point in the future, 87-11 needs to be added, it needs to be displayed between 87-10 and 87-20. I can't think of anyway to do this without shifting all the ids by one. Before I do that though, what could be another method that I'm not thinking of?
Split manufacturer number and store in 2 fields say mfgr1, mfgr2. (eg: split 87-11 as 87 and 11)
Then you can order the rows as you want:
select * from table1 order by mfgr1, mfgr2;
Note: if you are not storing mfgr1 and mfgr2 as integers then you might want to cast them as integers while querying.
This must also work:
select * from table1 order by convert(substring(mfgr,1,instr(mfgr,'-')-1), UNSIGNED INTEGER), convert(substring(mfgr,instr(mfgr,'-')+1), UNSIGNED INTEGER);