Jesal Mavadiya Jesal Mavadiya - 1 month ago 9
SQL Question

Sql order by ascending, some values are not in acending order

I have an SQL statement that orders the table in ascending order via the id, but when I do this, the table shows it like:

c-10
c-12
c-12
c-24
c-5
c-6
c-80
c-800
c-8000


As this shows that the top few and bottom few are in order but not the other part. Why is this? As I have tried ordering by my other fields in the table and they appear fine, it's just this column. Do you think this could be an input error or something else?

Many thanks for your help!

Answer

The order is OK, sql sorts the strings in lexical order.

As 2 (in c-24) comes before 5 (in c-5) it is sorted like that.

If you want to sort stings like this in the numeric order prepend them with zeros.

c-0010 c-0012 c-0012 c-0024 c-0005 c-0006 c-0080 c-0800 c-8000 will be sorted as you want it.