0

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.

3
  • The order you desire doesn't seem to make any sense, can you explain the "rules" for why the numbers should come back in that order? – xzilla Oct 14 '13 at 18:17
  • 1
    As an aside, it seem you have the items being stored as character data instead of numeric data, not sure if that's really what you want. – xzilla Oct 14 '13 at 18:17
  • xzilla thanks. My bad, Items type was VARCHAR. Now it returns desired result. – Adrian George Oct 14 '13 at 18:21
3

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.

1
  • yes you r right for that. – Anup Shah Oct 14 '13 at 18:41
0

My bad, Items type was VARCHAR.I changed it to INT. Now it returns desired result.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.