CODEWITHSUNDEEP
sqlsql-serversql-order-by
MindingData
MindingData

Reputation: 12508

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

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 SQL server, there is no issues.

I am currently using SQL Server 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.

EDIT: Someone brought up the issue of what I was doing with them after the datareader returned them...

I use a SortedList variable under the name of "results". 

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string Date = reader["Year"].ToString() + "/" + reader["Month"].ToString();
string Orders = reader["OrderCount"].ToString();
results.Add(Date, Orders);
}
}

Hope it helps.

Upvotes: 1

Views: 2704

Answers (3)

Cade Roux
Cade Roux

Reputation: 89721

Are you sure they are actually returned from your DataReader in other than expected (int) order? Trust but verify (in the debugger).

You haven't showed us how you are taking it out of the DataReader and what you are putting it into.

If you put them in some sort of ordered collection where the int column is converted (perhaps implicitly) into a string (say, Dictionary<string, string>), you'll see the same problem you saw in your DataGrid, regardless of the order they were returned from the DataReader. In that case, again, it's not really a SQL/SQLClient problem.

Upvotes: 2

Oleks
Oleks

Reputation: 32343

Have you tried using cast to string? STR function returns right-justified result, so the order should be correct in any case:

ORDER BY STR(DATEPART(YEAR, OrderDate), 4, 0) DESC,
         STR(DATEPART(MONTH, OrderDate), 2, 0) DESC

Upvotes: 0

mynameiscoffey
mynameiscoffey

Reputation: 15982

Have you tried using Cast(value as int)?

As in: order by cast(datepart(year, OrderDate), int)?

Upvotes: 1

Related Questions