How to get closest matching number?

Databases
1

Hi,

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

123 , 456
789 , 890
456 , 123

etc.

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

Thanks.

2

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
 order
    by abs(numberColumn-:userInput)
 limit 1
1 Like
3

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?

4

Hi,

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.

Thanks.

5

My column is a varchar not int

6

@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
7

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?

8

If you want it to be effective, take a look at the spatial functionality MySQL has:
http://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html

1 Like
9

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