CODEWITHSUNDEEP
mysql
Erik Andershed
Erik Andershed

Reputation: 363

Best match for multiple values in MySQL

Find the best match from multiple values and columns in MySQL.

Database:

id  star   point    price    
1   12     15       16
2   9      15       16
3   18     10       12

Find closes to this

star  point  price
10    15     14

Results

id: 2

Upvotes: 2

Views: 165

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You need a distance metric. But if you have one then you can just use order by and limit. So, if you use a Euclidean metric:

select t.*
from t
order by pow(star - 10, 2) + pow(point - 15, 2) + pow(price - 14, 2)
limit 1;

Note: The square root is not needed for ordering by the Euclidean metric.

Upvotes: 2

Related Questions