I want to sort my recordset in SQL2005 but Asc or Desc don't work. What to do?

[Pelle]

I have a very basic sql server 2005 db. I have a single table with two columns. The primary key column called "Col_ID" of course contains unique identifiers, in my case number from 1, 2, 3 and so on...

I use this code to retrieve my recordset and it works ok in Access 2003 but not now when I have migrated to sql2005.


Set rs = server.CreateObject("ADODB.recordset")
SQL = "SELECT * FROM MyTable ORDER BY Col_ID ASC"
rs.open SQL, con

Then this output is generated when I loop through the rs:

1 10 11 12 2 3 4 5 6 8 9

As you can see the computer believes that 10 is less than 9. I don't agree about that opinion. I want the system to output 123456789, 10, 11..well you know the rest..

Question of course. Which SQL query will make the database to understand that 10 and 11 come after 9 and not before?

Hope you can help me out.

[105881]

What is the data type of the Col_ID column? If you want to sort numbers the column must be a number or you have to cast the column as a number.

[Pelle]

First column (primary key) is/was nchar(10)
Second column is also nchar(10)
Third and fourth columns are both nvarchar of various lengths.

I tried to change the data type of my primary key to "numeric(18,0)" and now the machine understands that 10 is more than 9. That obviously solved my issue so thank you for your help.

However, I don't understand what "18,0" after the numeric means. Maybe you can give a brief explanation of that too.

[105881]

numeric(p , s)
p (precision) The maximum total number of decimal digits that can be stored

s (scale) The maximum number of decimal digits that can be stored to the right of the decimal point.


http://msdn2.microsoft.com/en-us/library/ms187746.aspx

[Pelle]

Ok, thanks.

So in other words I can't store a number like 5,01 because that will mean 2 digits to the right of the decimal point when no digits are allowed.

I will immediately have a look at the link that you sent.