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

Question

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.

@OrbMan, Pure semantics. Regardless of whether I want to go Asc or Desc, The issue will still be there. — MindingData
– MindingData, Commented May 6, 2010 at 2:35

Cade Roux · Accepted Answer · 2010-05-06 03:16:17Z

2

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.

answered May 6, 2010 at 3:16

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MindingData Over a year ago

Dang good point. Ok well I'm using a "SortedList", we are using .net 2.0 here. And I needed something with Key Value pairs, But preferable still had indexes. The variable Result is SortedPair Check Initial post for how im moving it into a sortedlist. However I will say I am placing them into the sorted list in order. Then iterating through the indexes...

MindingData Over a year ago

Ok that fixed it. I was under the impression that a SortedList would hold the index values in the order I gave it. But it was sorting itself based on whatever it felt like. Switched to a Dictionary<int, string> and it solved everything :)

Cade Roux Over a year ago

@Pyronaut - A SortedList is always ordered by the keys regardless of the order of insertion. Your problem is due to your use of YYYY/M instead of YYYY/MM to make the keys, resulting in the keys in this order: YYYY/1, YYYY/10, YYYY/11, YYYY/12, YYYY/2, YYYY/3 etc. Try instead (Year * 100 + Month).ToString() or keep as int: Year * 100 + Month.

mynameiscoffey · Accepted Answer · 2010-05-06 02:10:18Z

1

Have you tried using Cast(value as int)?

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

answered May 6, 2010 at 2:10

mynameiscoffey

16.1k5 gold badges35 silver badges46 bronze badges

2 Comments

Paul Kearney - pk Over a year ago

DATEPART returns ints already. Casting to an int shouldn't make a difference. msdn.microsoft.com/en-us/library/ms174420.aspx

MindingData Over a year ago

Yes I have :). Forgot I had used that one. As PK mentioned, Shouldn't make a difference.

Oleks · Accepted Answer · 2010-05-06 02:31:15Z

0

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

answered May 6, 2010 at 2:31

Oleks

32.4k11 gold badges80 silver badges134 bronze badges

1 Comment

MindingData Over a year ago

Didn't work :(. It could well be possibly something wrong with my own code, But as far as I can see, Everything I have done is fine. Is there an enviroment variable that could cause this. Seeing as it is only happening when im using it in VS

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related