Numeric Order By In Transact SQL (Ordering As String Instead Of Int)
- by Pyronaut
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.
