ansaurus

Question

MySQL Wrong ORDER BY

Answer 1

+1 A:

Try:

SELECT (player.k-player.d), player.cid, player.k, player.d, gg.gg_id, gg.name, gg.img, cc.cid, cc.name, cc.class, cc.gg_idFROM player LEFT JOIN cc ON cc.cid = player.cid LEFT JOIN gg ON gg.gg_id = cc.gg_id ORDER BY (player.k-player.d) DESC

I did a quick query of my own and the results appear to be unordered (despite the fact the were) until I added the SELECT (player.k-player.d). MySQL also complained about ommiting the table name in the ORDER BY clause.

Jason George 2009-11-20 19:10:03

i get a #1241 - Operand should contain 1 column(s)...if i put table(player.k-player.d), player (k-d) or database(player k - player d) i get unkown colums errors

2009-11-20 19:18:54

i did just for a try your simple "select player k - player d from xxx" and i noticed that some of the colums are giving me: 18446744073709551614...they both are INT and have legit values

2009-11-20 19:23:25

yeah its definitely something about negative values..i ordered by k+d and it worked..so know what?

2009-11-20 19:29:09

ok i changed the colums to FLOAT and know its working...why does mysql needs that, if im not inserting negative numbers there, just getting then in the query? they were INT(11) unsigned, now they are float unsigned

2009-11-20 20:26:56

My guess is since the numbers are unsigned MySQL was performing the subtraction as an unsigned operation and the result for negative numbers was an overflow. It's unclear, however, why float works. If you'd like to maintain your column as INT(11) unsigned you can do (CAST(player.k AS SIGNED) - CAST(player.d AS SIGNED)). You could potentailly still get an overflow here, however, if you exceed the maximum value for an unsigned int in either column.

Jason George 2009-11-20 21:09:24

hm thanks for your help, im going to make everything "FLOAT" since i only going to put numbers 0-999999 in the columns, i think theres no problem them

2009-11-20 21:27:00

ansaurus

tags:

views:

answers:

MySQL Wrong ORDER BY

related questions