the following sql query doesnt work as i expect. i want the records to be sorted order by pprice (i want it treated as integer) but it sorts them as if they were char values.
SELECT price,
@ham:=(price & 0x0FFFFFFF),
@cur:=(price >> 28 ),
if((@cur=1),(@ham),(if((@cur=2),(@ham*$dolr_val/100),(@ham*$euro_val/100)))) as pprice
from tbl1 order by pprice;
price is a 32bit INT field. first 4 bits used for currency info and the rest 28bits used for price. pprice is produced by multiplication of cur and ham
thanks for your interests.
sort by `value` problem?
Moderator: General Moderators
I tried to reproduce your problem and succeeded a couple of times and then all of a sudden it started sorting correctly. I cut, pasted and ran your query without changes. I know this isn't a solution but I just wanted to point out that there might be a bug in MySQL causing this. I use MySQL 3.23.49 on a Windows XP machine.
/josa
/josa
Okay, I've delved a bit deeper into the problem and found that MySQL sorts the result as a string only the first time you do the select. The second time it sorts correctly. Since this query don't have any keys to order by, the sorting is performed by creating a temporary file and dump the data to that file and then read it back sorted (it's a bit more complex, but the manual explains it better than I do). I tried to force MySQL to treat pprice as a numeric value and succeeded (on my machine anyway) by adding a zero to the result, like this:
SELECT price,
@ham:=(price & 0x0FFFFFFF),
@cur:=(price >> 28 ),
if((@cur=1),(@ham),(if((@cur=2),(@ham*$dolr_val/100),(@ham*$euro_val/100)))) + 0 as pprice
from tbl1 order by pprice;
/josa
SELECT price,
@ham:=(price & 0x0FFFFFFF),
@cur:=(price >> 28 ),
if((@cur=1),(@ham),(if((@cur=2),(@ham*$dolr_val/100),(@ham*$euro_val/100)))) + 0 as pprice
from tbl1 order by pprice;
/josa