|
|
Posted by Andy Hassall on 11/10/06 16:51
On Thu, 09 Nov 2006 15:40:43 GMT, Jeff <dont_bug_me@all.uk> 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
Navigation:
[Reply to this message]
|