I have a lot of columns, for simplicity lets say 3: col1, col2 and col3

each are of type DOUBLE


I need to select the closest one... so say I have 6 rows in this table, I need a query to order them by closest to say row# 5

So firstly, is this possible in one query, or do i need to select row#5 first and then do the query after I have the values of that col1, col2, col3?

Secondly... sometimes not all values can be filled... so col1 can be 0.0, col2 can be 22.5 and col3 can be 64 <-- lets say this is the row i want to match to

if any value is 0 (in the row i am matching against and the row i am matching from), i dont want to match it.. id rather match only the known values.


Sorry if i did a bad job explaining. Basically I need to compare numbers in many rows, find which is closest match and order them by that. Only issue is sometimes a value may be 0.0, in that case i dont want to match it.


Thanks for your help guys

I have a lot of columns, for simplicity lets say 3: col1, col2 and col3

each are of type DOUBLE


I need to select the closest one... so say I have 6 rows in this table, I need a query to order them by closest to say row# 5

So firstly, is this possible in one query, or do i need to select row#5 first and then do the query after I have the values of that col1, col2, col3?

Secondly... sometimes not all values can be filled... so col1 can be 0.0, col2 can be 22.5 and col3 can be 64 <-- lets say this is the row i want to match to

if any value is 0 (in the row i am matching against and the row i am matching from), i dont want to match it.. id rather match only the known values.


Sorry if i did a bad job explaining. Basically I need to compare numbers in many rows, find which is closest match and order them by that. Only issue is sometimes a value may be 0.0, in that case i dont want to match it.


Thanks for your help guys



I believe the easiest way to approach it would be something like:


Select your primary row first. Assign values to $c1, $c2, and $c3.

Then you do something like:

$sql = 'SELECT row_id, IF(col_1 = 0, null, abs('.$c1.' - col_1)) as dif_c1, IF (col_2 = 0, null, abs('.$c2.' - col_2)) as dif_c2, IF (col_3 = 0, null, abs('.$c3.' - col_3)) as dif_3 FROM table WHERE dif_c1 IS NOT NULL or dif_c2 IS NOT NULL or dif_c3 IS NOT NULL';

The sorting would need to be done in your code though, as to my knowledge MySQL does not allow you to OR sort (i.e. sort by c1, c2, or c3, whichever is closest to 0).

there has to be a way to sort it in mysql!