casting an order

I have a text field (MySQL) that contains integers. Without altering
the table, I'd like to order the results numerically rathjer that
alphanumerically.

ie:
1 2 3 4 5 6 7 8 9 10

rather than 1 10 2 3 4 5 6 7 8 9

I'm unsure of the select syntax.

Jeff

Re: casting an order

On Thu, 09 Nov 2006 15:40:43 GMT, Jeff wrote:

> I have a text field (MySQL) that contains integers. Without altering
>the table, I'd like to order the results numerically rathjer that
>alphanumerically.
>
>ie:
>1 2 3 4 5 6 7 8 9 10
>
>rather than 1 10 2 3 4 5 6 7 8 9
>
>I'm unsure of the select syntax.

mysql> create table t (c varchar(2));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t values ('1'), ('2'), ('3'), ('10'), ('11');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from t;
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 11 |
+------+
5 rows in set (0.00 sec)

mysql> select * from t order by c;
+------+
| c |
+------+
| 1 |
| 10 |
| 11 |
| 2 |
| 3 |
+------+
5 rows in set (0.00 sec)

mysql> select * from t order by cast(c as UNSIGNED INTEGER);
+------+
| c |
+------+
| 1 |
| 2 |
| 3 |
| 10 |
| 11 |
+------+
5 rows in set (0.00 sec)

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool