How to get closest matching number?


I am using mysql and i have a col. in which I am storing numbers like:

123 , 456
789 , 890
456 , 123


How can I search for the equal or the closet number which is input by the user from the db ?


I assume that the comma is just a presentation issue and that you do not store multiple values in one column.

select <columnsOfInterest>
  from t
    by abs(numberColumn-:userInput)
 limit 1
1 Like

Or you could

SELECT MIN([columnA])
  FROM t
WHERE [columnA] >= [User_Input]

I have to say that in this case I’m looking for the closest that is higher or equal to the existing user input

Is this homework?


No its not homework. I am working on a project in which I am saving coordinates, there would be 1000’s of coordinates and I have to search the closest one when the user inputs.


My column is a varchar not int

@molona Your solution only works one way, namely the smallest number that is bigger than the user input, but the requirement was closest to the input, so there could be a value in the database that is smaller than the input number and closer to the input number which your solution won’t find. @swampBoogie’s solution would find it though because that takes care of smaller through using absolute difference :slight_smile:

1 Like

Then, yes, you’re right… you want the closest… up or down. My solution will not work. That means that it is not a solution at all :stuck_out_tongue:

I’m curious though… Why not using floating numbers and use a varchar field?

If you want it to be effective, take a look at the spatial functionality MySQL has:

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.