CODEWITHSUNDEEP
mysqlsql
ahinkle
ahinkle

Reputation: 2261

MySQL - Find Closest Match in range of numbers

I'm working on a graph type application and I'm looking to find the best solution within a x,y axis start to end point.

MySQL Data:

enter image description here

Say I have the following: starting_x: 200 starting_y: 150 ending_x: 500 ending_y: 605

So I'm wanting to find the closest match between the above numbers to the database.

My query I'm working with now:

SELECT * FROM `graph` ORDER BY `start_pos_x`,`start_pos_y`,`end_pos_x`,`end_pos_y` ASC

I know this is not even close to what I'm trying to do but i'm having a hard time finding a solution on here.

Thanks!

Upvotes: 0

Views: 262

Answers (1)

Leo.W
Leo.W

Reputation: 569

This seems to be an algorithm question. First, you must define the closest match between the above numbers to the database. For example, I can define it as to find "the lowest sum of the squares of the differences between Xs and of Ys." Then, the solution might be something like:

Select 
  * 
From 
  `graph` 
Order by
  Power(starting_x-200, 2) + Power(starting_y-150, 2) 
  + Power(ending_x-500, 2) + Power(ending_y-605, 2) ASC

Upvotes: 1

Related Questions