Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question
This table has not only these 3 columns, it must have at least an Why don't you include the whole table structure? – ypercubeᵀᴹ May 10 '13 at 23:51

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.