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?

Is it a finite set of allowed values? If so use enum http://dev.mysql.com/doc/refman/5.0/en/enum.html — vinothkr, Oct 22 '10 at 09:04
Size is finite, but it is possible that new values are added, renamed, or deleted. (The size values can be changed in a admin backend) — ipsum, Oct 22 '10 at 09:29

score 1 · Accepted Answer · answered Oct 22 '10 at 09:40

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.

mysql order by integer and varchar

1 Answers1