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)

[AstarothSolutions]

You dont say which SQL you are using.... with SQL Server it would be something like:

SELECT TOP (1) NumberCol
FROM NearestNumber
ORDER BY ABS(NumberCol - @Query);

[cancer10]

I use mysql

[AstarothSolutions]

Well Top (1) becomes Limit (0,1) from memory, ABS() is the same in both and as your also probably a php person you will replace the @Query parameter with inline insertion of the number

[kmap]

"SELECT `NumberCol` FROM `NearestNumber` ORDER BY ABS(NumberCol -$n) limit(0,1)";

i think this is what he means and full marks

Regards

alex