CODEWITHSUNDEEP
mysql
Pradeep
Pradeep

Reputation: 141

order by nearest value in mysql

Need optimized query on the below search functionality

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 by the maximum possible ratings of the all required attributes

Note : - in first row there is only one possible matches found. - in second row nothing found, - in third row one matches found

So based on the number of matching, we need to order the records, If exact match not found, then relaxation on the search elements

Upvotes: 4

Views: 895

Answers (1)

Bohemian
Bohemian

Reputation: 425053

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 matches. Ordering desc returns them "closest match first"

Upvotes: 3

Related Questions