Hi Ariel,
It works fine for me
localhost.test>show create table mytest\G
*************************** 1. row ***************************
Table: mytest
Create Table: CREATE TABLE `mytest` (
`id` int(11) NOT NULL auto_increment,
`test_col` varchar(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
5.0.18-max on port mysql.sock as [EMAIL PROTECTED]
localhost.test>
5.0.18-max on port mysql.sock as [EMAIL PROTECTED]
localhost.test>select * from mytest order by test_col;
+----+----------+
| id | test_col |
+----+----------+
| 1 | 1 |
| 10 | 10 |
| 12 | 11 |
| 2 | 2 |
| 11 | 20 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+----------+
12 rows in set (0.01 sec)
5.0.18-max on port mysql.sock as [EMAIL PROTECTED]
localhost.test>select * from mytest order by CAST(test_col as unsigned);
+----+----------+
| id | test_col |
+----+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
| 12 | 11 |
| 11 | 20 |
+----+----------+
12 rows in set (0.00 sec)
Regards
---------------------------------------------------------------
********** _/ ********** David Logan
******* _/ ******* ITO Delivery Specialist - Database
***** _/ ***** Hewlett-Packard Australia Ltd
**** _/_/_/ _/_/_/ **** E-Mail: [EMAIL PROTECTED]
**** _/ _/ _/ _/ **** Desk: +618 8408 4273
**** _/ _/ _/_/_/ **** Mobile: 0417 268 665
***** _/ ******
****** _/ ******** Postal: 148 Frome Street,
******** _/ ********** Adelaide SA 5001
Australia
i n v e n t
---------------------------------------------------------------
-----Original Message-----
From: Ariel Sánchez Mora [mailto:[EMAIL PROTECTED]
Sent: Friday, 17 February 2006 11:29 AM
To: mysql@lists.mysql.com
Subject: Hi, newbie question on a select statement
I searched the other lists and couldn't find one that was more appropiate for
this question; if there is, please tell me so :)
I am monitoring networking equipment and so far I'm saving all my data in a
MySQL database (hence an email to this list). I'm using only VARCHARs because
the SNMP agent returns only strings and I didn't find enough a reason for
converting the different types of answers, since most string comparations are
donde correctly; I received both text and numbers from the monitoring tasks and
wanted to keep it simple.
However when I execute this select statement I am getting this problem:
select info_oficina,valorSNMP from ultimas_respuestas_snmp where
columna_donde_guardar='USO_CPU_1min' order by valorSNMP desc limit 10;
info_oficina valorSNMP
CSF Desamparados error
Periferica Palmares 4
CSF San Pedro 4
Sucursal Guapiles 4
Periferica Pentagono San Pablo 30
Periferica Tibas 3
Periferica Buenos Aires 3
Sucursal Turrialba 3
Ventanilla Florencia 3
CSF del Sur (Ciudad Neilly) 3
Where info_oficina, valorSNMP are columns, ultimas_respuestas_snmp is my table,
etc.
The summary question is:
given a column with the numbers 0 to 99 of type varchar, how can i tell MySQL
to order it as if they were integer values, so that instead of
99,98,97,96,95,94,93,92,91,90,9,89
I'd have
99,98,97,96,95,94,93,92,91,90,89,88
Apparently, CAST() does not have an effect (but feel free to prove me wrong)
Thanks!
Ariel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
