Select Closest Numerical Value with MySQL Query

Select closest numerical value with MySQL query

One option would be something along the lines of:

select   the_value,
         abs(the_value - 14) as distance_from_test
from     the_table
order by distance_from_test
limit 1

To select a random record, you can add , rand() to the order by clause. The disadvantage of this method is that you don't get any benefit from indices because you have to sort on the derived value distance_from_test.

If you have an index on the_value and you relax your requirement for the result to be random in the case of ties, you can perform a pair of limited range queries to select the first value immediately above the test value and the first value immediately below the test value and pick whichever is closest to the test value:

(
select   the_value
from     the_table
where    the_value >= 14
order by the_value asc
limit 1
)
union
(
select   the_value
from     the_table
where    the_value < 14
order by the_value desc
limit 1
)
order by abs(the_value - 14)
limit 1

Select closest value from table using mysql query

You can use a subquery which returns the min max_value that is greater than or equal to the value you search for, or if it does not exist the max max_value that is smaller to that value:

select * from tablename
where max_value = coalesce(
  (select min(max_value) from tablename where max_value >= 350),  
  (select max(max_value) from tablename where max_value < 350)
)

See the demo.

How can I return the nearest value from mysql

What a nice question :)

SELECT VALUE FROM mytable ORDER BY ABS(VALUE-1999) LIMIT 1

Of course that 1999 will be a variable in real.

Fiddle

Finding the nearest numeric match in a database to what a user has inputted in php

try this

SELECT * FROM `table_test1` where `price` > 80 order by `price` asc limit 1

SQL: Find closest number to given value with ties

If your driver supports nested queries:

SELECT * 
FROM CARS 
WHERE ABS(price - $price) = ( SELECT MIN(ABS(price - $price)) FROM CARS )

Find closest numeric value in database

get the difference between the area and your input, take absolute value so always positive, then order ascending and take the first one

SELECT TOP 1 * FROM [myTable] 
WHERE Name = 'Test' and Size = 2 and PType = 'p'
ORDER BY ABS( Area - @input ) 

MySQL select closest value

Just use ORDER BY and LIMIT cleverly.

   WHERE weight < max_weight
ORDER BY max_weight ASC 
   LIMIT 1

Find the closest number to value not working (SQL)

You should have a column name in the SELECT clause instead of TOP 1.

Presumably, you want:

SELECT price
FROM products
ORDER BY ABS(price - :price) 
LIMIT 1;

Note that this uses query parameter to pass the target price value rather than concatenating it into the query string. You can have a look at this famous SO post to learn why and how to avoid SQL injection.

---

Related Topics