homepage Welcome to WebmasterWorld Guest from 50.19.144.243
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Accredited PayPal World Seller

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Ordering column with negative and positive decimal numbers in MySQL
Ordering column with negative and positive decimal numbers in MySQL
deimon




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

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




msg:4459955
 4:08 pm on May 31, 2012 (gmt 0)

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




msg:4460023
 6:04 pm on May 31, 2012 (gmt 0)

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




msg:4460411
 3:52 pm on Jun 1, 2012 (gmt 0)

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




msg:4462675
 10:01 pm on Jun 7, 2012 (gmt 0)

Are the commas in your example typos?

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved