Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
yes you r right for that. –  Anup Shah Oct 14 '13 at 18:41
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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