I would like to query a range of criteria on multiple columns in MySQL

Question

I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :

We have 3 columns as "Fast" "Smart" "Sharp" and they are of type boolean/int I have 1000 records in the table, as an example, I list 5 here.

Fast  |  smart  |  Sharp
------------------------
0         1          1
1         1          1
0         0          1
1         1          1
1         0          0

and let's say we wanna search for people who are smart and sharp as 0 1 1 but if there is no record with the value of 0 1 1 we want to get the closest to it which is 1 1 1 or 1 0 1 or etc. So now when I search for it, I get the exact value, if the value doesn't exist, I won't get anything back from DB. So how can I achieve this and if I get a bunch of result, how can I sort them from the closets to my query to the feartest ?

Answer 1

Assuming you want to match against 1 1 1 you could do something like this:

set @fast:=1;
set @smart:=1;
set @sharp:=1;

SELECT if(`fast`=@fast,1,0) + if(`smart` =@smart,1,0) + if(`sharp`=@sharp,1,0) as score FROM `table` order by score DESC

That should return the exact matches with score 3, people with 2 matches with score 2 and so on.