From: sheeri kritzer
Date: February 20 2006 5:46pm
Subject: Re: Hi, newbie question on a select statement
List-Archive: http://lists.mysql.com/mysql/194990
Message-Id: <24cb9b4b0602200946x3a628d58g366da4de2ef8251e@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Perhaps you were using CAST() incorrectly?  What was your attempt?

my test table:

show create table ultimas_repuestas;
+-------------------+------------------------------------------------------=
-----------------------------------------------------------+
| Table             | Create Table                                   =20
                                                               |
+-------------------+------------------------------------------------------=
-----------------------------------------------------------+
| ultimas_repuestas | CREATE TABLE `ultimas_repuestas` (
  `valorSNMP` varchar(3) default NULL
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 |
+-------------------+------------------------------------------------------=
-----------------------------------------------------------+
1 row in set (0.00 sec)

then I put some values in it:

mysql> select * from ultimas_repuestas order by valorSNMP;
+-----------+
| valorSNMP |
+-----------+
| 1         |
| 10        |
| 11        |
| 12        |
| 13        |
| 14        |
| 2         |
| 3         |
| 4         |
| 5         |
| 6         |
| 7         |
| 8         |
| 9         |
+-----------+
14 rows in set (0.02 sec)

Then I tried a CAST() statement:

mysql> select * from ultimas_repuestas order by CAST(valorSNMP as
SIGNED INTEGER);
+-----------+
| valorSNMP |
+-----------+
| 1         |
| 2         |
| 3         |
| 4         |
| 5         |
| 6         |
| 7         |
| 8         |
| 9         |
| 10        |
| 11        |
| 12        |
| 13        |
| 14        |
+-----------+
14 rows in set (0.00 sec)

works just fine in MySQL 5.0.

not that you gave the version #.....although this works fine, too:

mysql> select * from ultimas_repuestas order by valorSNMP+0;
+-----------+
| valorSNMP |
+-----------+
| 1         |
| 2         |
| 3         |
| 4         |
| 5         |
| 6         |
| 7         |
| 8         |
| 9         |
| 10        |
| 11        |
| 12        |
| 13        |
| 14        |
+-----------+
14 rows in set (0.00 sec)

Hope this helps!  Next time, instead of saying "this didn't work" give
the example, and what you got back (what does 'doesn't work' mean?=20
did you get an error?  Or it didn't return things in the right order?)

-Sheeri

On 2/16/06, Ariel S=E1nchez Mora <Arsanchez@stripped> wrote:
> 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 be=
cause the SNMP agent returns only strings and I didn't find enough a reason=
 for converting the different types of answers, since most string comparati=
ons are donde correctly; I received both text and numbers from the monitori=
ng 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=3D'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 M=
ySQL 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 wr=
ong)
>
> Thanks!
>
> Ariel
>
>