Mysql – SORT, ORDER by Number

MySQL

I have the following table:

ID     Name    Items
------------------------------------- 
1      John    7
2      Peter   533
3      Chang   13
4      Mike    9100

I want to order it by Items. I used ORDER BY items ASC, but it returns:

ID     Name    Items
------------------------------------- 
3      Chang   13
2      Peter   533
1      John    7
4      Mike    9100

I want to return:

ID     Name    Items
------------------------------------- 
1      John    7
3      Chang   13
2      Peter   533
4      Mike    9100

I think this might be a silly question, but I really don't have any ideas about how to solve this.

Thanks.

Best Answer

Only explanation I can think of that result, if you are sorting with Order By Items ASC as you said, is that Items column is of character datatype (either VARCHAR or CHAR) instead of numeric.

13, 533, 7, 9100 are in lexicographical order, not numerical order.

Check the data type of that column.

Related Solutions

Mysql: order by field or select union

If you need a specific order, you should specify it with the ORDER BY clause.

The most efficient way to perform an order-by is to ORDER BY somefield.

If you need those records sorted as 3,1,2, then perhaps you should add a field sortorder that is ordered the way you want so you can simply do ORDER BY sortorder.

MySQL – Select Rows in a Specific Order

This is pretty clumsy but will perhaps give you some thoughts. The idea is to number the rows and pick from there using modulo:

select x from (
    select @n := @n + 1 as n, x 
    from t, ( select @n := 0 ) as dummy
) as u 
where mod(n,6) between 0 and 1 or @n = n

Edit: As observed by @hassansin @n will have the value of the last n. Therefore the union of the last n can be replaced with the predicate @n = n

Best Answer

Related Solutions

Mysql: order by field or select union

MySQL – Select Rows in a Specific Order

Related Question