MySQL calculate an complicated expression

Question

Hi i have 2 fields in database and i would like to find the difference of them in percentage. So mathematical the formula should be

ABSOLUTE((field1-field2)/(MAXIMUM(field1, field2)))

the problem is I dont know how to ask for maximum of 2 numbers. Since MAX in sql returns the max of column.

Answer 1

maybe someting like:

if (field1 > field2, (field1 - field2)/field1, (field2 - field1) / field1)

Answer 2

SELECT  ABS(field1 - field2) / GREATEST(field1, field2)
FROM    mytable

Answer 3

How about simply this

ABS((field1-field2)/(GREATEST(field1, field2)))

Answer 4

Try an IF:

SELECT IF(field1 < field2, field1, field2).

Answer 5

You are looking for the function named GREATEST, since MAX is used elsewhere in SQL.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_greatest