Numeric Order By In Transact SQL (Ordering As String Instead Of Int)

Posted by Pyronaut on Stack Overflow See other posts from Stack Overflow or by Pyronaut
Published on 2010-05-06T02:07:24Z Indexed on 2010/05/06 2:18 UTC
Read the original article Hit count: 448

Filed under:
|
|

I have an issue where I am trying to order a result set by what I believe to be a numberic column in my database. However when I get the result set, It has sorted the column as if it was a string (So alphabetically), instead of sorting it as an int.

As an example. I have these numbers,

1 , 2, 3, 4, 5, 10, 11

When I order by in Transact SQL, I get back :

1, 10, 11, 2, 3, 4, 5

I had the same issue with Datagridview's a while back, And the issue was because of the sorting being done as if it was a string. I assume the same thing is happening here.

My full SQL code is :

SELECT  TOP (12) DATEPART(YEAR, [OrderDate]) AS 'Year',  DATEPART(MONTH, [OrderDate]) AS 'Month' , COUNT(OrderRef) AS 'OrderCount'
FROM [Order]
WHERE [Status] LIKE('PaymentReceived') OR [Status] LIKE ('Shipped')
GROUP BY  DATEPART(MONTH, [OrderDate]), DATEPART(YEAR, [OrderDate])
ORDER BY DATEPART(YEAR, OrderDate) DESC, DATEPART(MONTH, OrderDate) desc

DO NOTE The wrong sorting only happens when I cam calling the function from Visual Studio. As in my code is :

using (SqlConnection conn = GetConnection())
            {
                string query = @"SELECT  TOP (12) DATEPART(YEAR, [OrderDate]) AS 'Year',  DATEPART(MONTH, [OrderDate]) AS 'Month' , COUNT(OrderRef) AS 'OrderCount'
                                FROM [Order]
                                WHERE [Status] LIKE('PaymentReceived') OR [Status] LIKE ('Shipped')
                                GROUP BY  DATEPART(MONTH, [OrderDate]), DATEPART(YEAR, [OrderDate])
                                ORDER BY DATEPART(YEAR, OrderDate) DESC, DATEPART(MONTH, OrderDate) desc";
                SqlCommand command = new SqlCommand(query, conn);
                command.CommandType = CommandType.Text;

                using (SqlDataReader reader = command.ExecuteReader())

etc. When I run the statement in MSSQL server, there is no issues.

I am currently using MSSQL 2005 express edition, And Visual Studio 2005.

I have tried numerous things that are strewn across the web. Including using Convert() and ABS() to no avail.

Any help would be much appreciated.

© Stack Overflow or respective owner

Related posts about sql

Related posts about mssql