nearest number

[cancer10]

If you have a column, of data type = INT, with the following numbers
as separate rows.
1
3
4
5
7
10

How can you write a sql statement where you can calculate the
nearest number to any give input.
Example , if the input is 9 ? (the answer is 10)
If the input is 8 (the anwer is 7)

[petr.pavel]

Hi there,
try something along these lines:

SELECT columnName, ABS(columnName - 9) AS distance 
ORDER BY distance LIMIT 1

Could be that ORDER BY won't accept alias distance in which case you'll have to repeat the ABS() code. The trick is in calculating the distance of your number (9) and your column values.

In PHP you then use something like:
$result = mysql_query("SELECT ... ABS(columnName - $myNumber) ...");
list($closestNumber) = mysql_fetch_row($result);

Let me know if this helps.
Petr 'PePa' Pavel

[cancer10]

what does ABS do?

[petr.pavel]

what does ABS do?

Calculates absolute value: ABS(-10) = 10, ABS(10) = 10