mysql order by integer and varchar

Question

I got a field with sizes(VARCHAR). The sizes can have int and string values, e.g. (1, 2, 3-4, X, XL, M, ...). When i use the normal order by function of mysql the output is the following: 1,10,11,2, 44-46, L, M, S, XL, XXL The values that does not contain a number are sorted as i want to do it. (I know that the values are sorted as a string and the result is correct). But i want to order the values like this: 1, 2, 3, 3-4, L, M, S, XL, XXL Also a more "logical" order. Is this possible with msql?

Answer 1

The most elegant and flexible solution is to put the sizes in a separate table and use JOINs. For example a table definition can look like this:

CREATE TABLE sizes (
  id INT PRIMARY KEY AUTO_INCREMENT,
  ord INT,
  name VARCHAR(8)
);

And in those tables where you previously used sizes, you should use size_id:

CREATE TABLE tshirts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  colour VARCHAR(16),
  price INT,
  size_id INT
);

And when you query, you JOIN it with sizes, order by sizes.ord and display sizes.name to the user like this:

SELECT colour, price, sizes.name FROM tshirts
  JOIN sizes ON tshirts.size_id = sizes.id
  ORDER BY sizes.ord;

This way, the order of sizes can be independent of the name, and you can still manage them using the separate table.