CODEWITHSUNDEEP
mysqlinsert
sharf
sharf

Reputation: 2143

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?

Upvotes: 0

Views: 196

Answers (5)

manuskc
manuskc

Reputation: 794

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);

Upvotes: 1

Mark Taylor
Mark Taylor

Reputation: 1851

Other answers assume that all manufacturer numbers are are ##-#... (*two digits - one or more digits). That may not be the case, or it may be true now but not guaranteed. This is an issue of database design rather than "what algorithm can keep the rows ordered. If manufacturer ID's are always two numeric components that should be sorted numerically, they should be stored as 2 numeric columns and formatted as one for display purposes. It really doesn't make sense to think about "inserting rows in order." Primary keys should never need to be changed. It sounds like you are using auto-increment for ID. In that case, the ID has no meaning at all other than to uniquely identify a row. Queries will need an ORDER BY clause to put the rows in order. The trade-off is whether you split that mfgr key into 2 columns before storing the rows, or if you use functions to split it on the fly in the ORDER BY.

Upvotes: 0

Anton
Anton

Reputation: 4018

It might be slower, but you can create your own function that:

  1. Splits the field using the - symbol into the two numbers (still treated as strings).
  2. Use the MySQL LPAD() function to make sure both numbers are padded with the same number of zeroes on the left.
  3. Recombine the two padded number strings and return the result in the function.
  4. Apply the function on the WHERE clause of your query.

I would only use a method like this for some ad-hoc reports though, otherwise split into two columns if the query will be run a lot.

Upvotes: 0

cheesemacfly
cheesemacfly

Reputation: 11762

You should be able to do it using someting like

SELECT * FROM table1 ORDER BY CAST(`mfgr` AS SIGNED) DESC

Upvotes: 0

Nir Alfasi
Nir Alfasi

Reputation: 53525

store the manufacturer number without the - as one number and show the - only when you display it on screen (after the first tow digits - insert back the -).

Upvotes: 0

Related Questions