Ordering column with negative and positive decimal numbers in MySQL

[deimon]

Can you help me anybody ?
I have a MySQL db and table thers column have a decimal (positive and negative) and i need this column ashort.

For example i have in column this values:

-20.00
-19.50
-19.00
-18.50
..
-0.7
0.0
+0.5
+1.0 ..etc

but with query

ORDER BY column ASC

makes shortest words.
with

ORDER BY CAST( column AS DECIMAL ) ASC

makes :

-1,25
+0,75
0,00
-0,50
-0,75
+0,50

any idea ? .. many thanks

[rocknbil]

Are the commas in your example typos?

Are your field types decimal? By the look of it, it's not, and you'll get those results if it's a textual field.

You don't need a + by positive values.

I hope your field name is not "column," column is a reserved word and you'll have to use backticks (NOT quotes) to access it.

select `column` from `tablename` order by `column` asc;

create table deci_test (decivalues decimal(12,2) not null default '0.00');

insert into deci_test (decivalues) values ('-20.00');
insert into deci_test (decivalues) values ('0.5');
insert into deci_test (decivalues) values ('-19.00');
insert into deci_test (decivalues) values ('1.0');
insert into deci_test (decivalues) values ('-18.50');
insert into deci_test (decivalues) values ('-19.50');
insert into deci_test (decivalues) values ('-0.7');
insert into deci_test (decivalues) values ('0.0');

select decivalues from deci_test order by decivalues asc;
+------------+
| decivalues |
+------------+
| -20.00 |
| -19.50 |
| -19.00 |
| -18.50 |
| -0.70 |
| 0.00 |
| 0.50 |
| 1.00 |
+------------+
8 rows in set (0.02 sec)

If they're not decimal, make them so.

alter table yourtable change yourfield yourfield decimal (12,2) not null default '0.00';

[deimon]

I'm sorry, but I forgot that the column can contain text (its storage for atributes of products, for.ex color, size etc.) => column must be Varchar :-(

[rocknbil]

Well, that is a poor database design because it robs you of the power of what mySQL can do. If you're going to store attributes associated with products, those attributes should be in joined tables, not in single strings you need to break apart with your programming.

For example . . . . [webmasterworld.com]

... and again [webmasterworld.com]

Given that, otherwise you'll have to devise some way of pulling all the fields, and in your programming breaking the data apart into arrays and sorting them by the digital element as a key. Really inefficient way to go about it.

[Dijkgraaf]

Are the commas in your example typos?

Probably not, see European Number format.
They have 1.234,56 as opposed to 1,234.56