sharf sharf - 1 year ago 73
MySQL Question

How to go about ordering a mysql table by inserting a row between two others

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?

Answer Source

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.

Soln 2:

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download