ORDER BY problems depending upon type

[menuserve]

I want my query to ORDER BY number, but I have a problem...

I have the following numbers...

1
4
22
28T

I my number column is of type int, I lose the "T" in 28T.

If I make it of type CHAR, it orders it as follows:

1, 22, 28T, 4

4 should be before 22. Can anyone help with this? One way I can get it to work is to keep it as a CHAR and enter 1 and 4 as 01 and 04, but i don't want to have to do that.

Anyone?

[r937]

ORDER BY CAST(colname AS UNSIGNED)

note that NUMBER is a reserved word and you should probably change the column name

[DaveMaxwell]

If you want to treat it like a numeric, then the field should be numeric. That means whatever situation makes the T in the field should be removed. If you've got multiple sub-groups under one number, make it an additional field, then sort it by both of them.

In other words, change your structure to be like this:

OrderByField int,
OrderBySubField char

Then your order by portion of the SQL statement would be ORDER BY OrderByField, OrderBySubField

[DaveMaxwell]

ORDER BY CAST(colname AS UNSIGNED)

note that NUMBER is a reserved word and you should probably change the column name

Won't the cast blow out on the 28T, though? Curious minds want to know....

[r937]

Won't the cast blow out on the 28T, though? Curious minds want to know....

really curious minds would run a quick test...

SELECT CAST('28T' AS UNSIGNED)

[DaveMaxwell]

Good thing I'm not really curious then, cause unsigned doesn't work in SQL Server 2005.

Msg 243, Level 16, State 1, Line 1
Type UNSIGNED is not a defined system type.

[r937]

oh, now i'm embarrassed, because CAST AS UNSIGNED is mysql syntax

the original poster did not indicate which database system this is, so you shouldn't assume sql server either

[DaveMaxwell]

oh, now i'm embarrassed, because CAST AS UNSIGNED is mysql syntax

the original poster did not indicate which database system this is, so you shouldn't assume sql server either

I didn't, which is why I answered the OP's question the way I did.

[menuserve]

I'm using mysql

[r937]

I'm using mysql

we have a separate forum for mysql

[menuserve]

r937,

Thank you, that worked perfectly. It properly orders a VARCHAR column that has numbers and letters in it!