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)

[tiago]

Did you already tryed:

table_of_vals
vals
3
6
10
8

if you want the nearest number of 9 (critic case):
<?

$nearest2find = 9;

$query = mysql_query( "select vals from table_of_vals where vals > '".$nearest2find."' or vals < '".$nearest2find."' LIMIT 2" );
while( $row = mysql_fetch_array( $query ) ) {
$nearestnumbers[] = $row['vals'];
}

// At this point we have $nearestnumbers with 2 nearest values
// at our example, this query will return 8 and 10

// then you need to check the absolute distance between the number you had choosed (9)

$first_distance = abs( $nearestnumbers[0] - $nearest2find );
$second_distance = abs( $nearestnumbers[1] - $nearest2find );

if( $first_distance > $second_distance ) {
echo "The nearest number is " . $second_distance;
}
else {
echo "Second distance is greater or equal to first_distance<br />";
echo "The nearest number is " . $first_distance;
}

?>

I didn't tested this script.

[sorry my poor english.