sort by `value` problem?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mays
Forum Newbie
Posts: 3
Joined: Wed Jul 10, 2002 5:45 am

sort by `value` problem?

Post by mays »

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.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

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
mays
Forum Newbie
Posts: 3
Joined: Wed Jul 10, 2002 5:45 am

Post by mays »

so
anyone can help me?

or any suggest, who i can ask about this problem?
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by 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
Post Reply