Sort by closest value in mysql

Question

An optimized query is required for the search function below

Product table

Product att1 att2 att3 att4 att5 p1 1 2 2 3 2 p2 4 1 1 5 5 p3 5 3 5 3 2 p4 4 4 2 1 1 

if user search att1 = 3, att2 = 2 and att3 = 5

then we need to display all the records in order the maximum possible ratings of all the necessary attributes

Note. - In the first line, only one possible match was found. - nothing was found in the second line, - one match was found in the third line

Therefore, based on the number of matches, we need to order records. If no exact match is found, then relaxation when searching Elements

Answer 1

 select * from product order by (att1 = 3) + (att2 = 2) + (att3 = 5) desc 

This works because in mysql true is 1 and false is 0 , so summing the conditions gives you the number of search criteria. Order desc returns their "closest match first"