Hi Sheeri, you are correct in both the MySQL tips and in your suggestion that I
should include my test when I think something doesn't work ;) Thanks a lot!
I realize now that when I've sent a thank you note, I have only sent it to the
responder instead of all the list; my problem was sorted out (no pun intended)
and I am deeply thankful to all of you for your time in reading the email.
Warm thanks to David, Leo and Sheeri for their tips and examples; hope I can
soon contribute back :) A small thing: I think I found a bug in Delphi 7 with
dbExpress that makes both these correct solutions behave differently; It
probably behaves this way because the +0 converts the varchars into some
numeric form and Delphi can order them correctly. I explain better in the
forwarded email below; in a nutshell, Delphi re-ordered incorrectly the column
when I used CAST() but not when I used +0.
Ariel
-----Mensaje original-----
De: Ariel Sánchez Mora
Enviado el: lunes, 20 de febrero de 2006 10:30
Para: 'leo huang'; 'Logan, David (SST - Adelaide)'
Asunto: RE: Same question, better example
Thanks a lot Leo, that worked wonders. David Logan from HP Australia corrected
my use of CAST and this also works, in MySQL:
select stri from prueba order by CAST(stri as unsigned) desc;
Alas, dbexpress in Delphi 7 had problems when presenting it in a grid (it
re-ordered what MySQL gave, in the old manner, although everywhere else I
tried, the results were displayed correctly). I had decided not to bother David
anymore, since I could change my query and use a > (some_value), which also
worked fine for my project. The dbexpress problem does not happen in your case,
so your solution is greatly appreciated, and now I have more options. Thank you
a lot, both, for your time! I hope that if you ever have a similar problem with
dbExpress or Delphi, you can use this information :)
Thanks again,
Ariel
-----Mensaje original-----
De: leo huang [mailto:[EMAIL PROTECTED]
Enviado el: lunes, 20 de febrero de 2006 2:47
Para: Ariel Sánchez Mora
CC: mysql@lists.mysql.com
Asunto: Re: Same question, better example
Ariel,
You can try this:
mysql>select stri from prueba order by stri+0 desc;
Leo Huang
2006/2/17, Ariel Sánchez Mora <[EMAIL PROTECTED] <mailto:[EMAIL
PROTECTED]> >:
mysql> describe prueba;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| inte | int(2) | YES | | NULL | |
| stri | char(2) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set ( 0.00 sec)
mysql> select * from prueba;
+------+------+
| inte | stri |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
+------+------+
10 rows in set (0.00 sec)
--> Is there a way I can make this:
mysql> select stri from prueba order by stri desc;
+------+
| stri |
+------+
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 10 |
| 1 |
+------+
10 rows in set (0.00 sec)
--> come out like this:
mysql> select inte from prueba order by inte desc;
+------+
| inte |
+------+
| 10 |
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
+------+
10 rows in set (0.00 sec)
I'm using MySQL 4.1.14 in windows 2000.
Thanks!
Ariel
-----Mensaje original-----
De: sheeri kritzer [mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ]
Enviado el: lunes, 20 de febrero de 2006 11:47
Para: Ariel Sánchez Mora
CC: mysql@lists.mysql.com
Asunto: Re: Hi, newbie question on a select statement
Perhaps you were using CAST() incorrectly? What was your attempt?
my test table:
show create table ultimas_repuestas;
+-------------------+-----------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------------------+-----------------------------------------------------------------------------------------------------------------+
| ultimas_repuestas | CREATE TABLE `ultimas_repuestas` (
`valorSNMP` varchar(3) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------------+-----------------------------------------------------------------------------------------------------------------+
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?
did you get an error? Or it didn't return things in the right order?)
-Sheeri
On 2/16/06, Ariel Sánchez Mora <[EMAIL PROTECTED]> 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 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
