RE: Hi, newbie question on a select statement

[David (SST - Adelaide) Logan]

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 root@localhost
localhost.test>

5.0.18-max on port mysql.sock as root@localhost
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 root@localhost
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: david.logan@stripped
****   _/  _/  _/  _/   ****  Desk:   [phone]
****  _/  _/  _/_/_/    ****  Mobile: [phone]
*****        _/       ******    
******      _/      ********  Postal: 148 Frome Street,
********   _/     **********          Adelaide SA 5001
                                      Australia 
i    n    v    e    n    t                                   
---------------------------------------------------------------

-----Original Message-----
From: Ariel Sánchez Mora [mailto:Arsanchez@stripped] 
Sent: Friday, 17 February 2006 11:29 AM
To: mysql@stripped
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