Welcome to WebmasterWorld Guest from 54.166.55.74

Forum Moderators: open

Message Too Old, No Replies

Ordering column with negative and positive decimal numbers in MySQL

Ordering column with negative and positive decimal numbers in MySQL

   
12:37 pm on May 31, 2012 (gmt 0)

5+ Year Member



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
4:08 pm on May 31, 2012 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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';
6:04 pm on May 31, 2012 (gmt 0)

5+ Year Member



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 :-(
3:52 pm on Jun 1, 2012 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
10:01 pm on Jun 7, 2012 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



Are the commas in your example typos?

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

Featured Threads

Hot Threads This Week

Hot Threads This Month