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)
Jump to PostHi there,
try something along these lines:SELECT columnName, ABS(columnName - 9) AS distance ORDER BY distance LIMIT 1Could 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 …
Hi there,
try something along these lines:
SELECT columnName, ABS(columnName - 9) AS distance
ORDER BY distance LIMIT 1Could 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
what does ABS do?
what does ABS do?
Calculates absolute value: ABS(-10) = 10, ABS(10) = 10
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.