ORDER BY problems depending upon type

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?

Code:

---

ORDER BY CAST(colname AS UNSIGNED)

---

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

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

Quote:

---

Originally Posted by r937

Code:

---

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....:shifty:

Quote:

---

Originally Posted by DaveMaxwell

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

---

really curious minds would run a quick test...

Code:

---

SELECT CAST('28T' AS UNSIGNED)

---

:D

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

Quote:

---

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

---

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

;)

Quote:

---

Originally Posted by 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

;)

---

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

I'm using mysql

Quote:

---

Originally Posted by menuserve

I'm using mysql

---

we have a separate forum for mysql :)

r937,

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