order by clause acting funny

[jimny]

I have a cloumn nDays type nVarchar(50)in a table
some entries are like "1 & 2" and some are single numbers like "12" or "1".
If I execute "SELECT news.nDays FROM News ORDER BY nDays"
it orders the results by the leading digit.

For example, five rows may contain thevalues:

1,1 & 2,5,11,23

returning the results:

1
1 & 2
11
23
5

anyone know why this happens and how to get it to order normally??

thanks

 

[SQLBill]

ORDER BY does what is called an alphabetic sort. For letters, that's obvious how the order will happen...

For numbers, it works the same way...first all numbers beginning with a 1. That's 1, 11, 12, 13, 111, 1111 etc. They all BEGIN with a 1. Then those are ordered by the next number, and so forth. Then all the numbers beginning with a 2 because that comes AFTER all numbers beginning with a 1.

For better understanding, use letters instead of numbers.
1 = a, 2 = b, etc...

Order by does this;

a
aardvark
ant
b
baa
box


Using numbers for the letters, that's this:
1
1 1 18 4 ......
1 15 20
2
2 1 1
2 15 24

Which is what you are seeing....all the 1's then all the 2's.

You would need to write a script that would add leading zero's to the numbers, then the order by would work like you want it to work.

0001
0002
0010
0100

-SQLBill

 

[jimny]

thanks SQLBill

will attempt...