MySQL Query - Order by best matches

Hi All,

I’m certainly no mySQL guru so i’m looking for some assistance with a query. The query below brings back 1 exact match fine but the data in the table is not fully complete. What I want to do is return the all results in order of ‘all 5 match’, ‘4 match’, ‘3 match’, ‘2 match’, ‘1 match’.

make_id=$make AND
model_id=$model AND
variant_id=$variant AND
engine_id=$engine AND

I hope I’ve explained it ok! Any help would be appreciated. Thx in advance.


To get all results, get rid of the LIMIT

To order them based on the number of matching columns is a bit trickier I think. No time to think about it now, maybe someone else knows the solution?

Thanks for the advice, I’ve gone down the multiple queries and PHP route for know but will look around to see if i can get it down to the 1 query.


select <columnsOfInterest>,
       case when make_id = $make then 1 else 0 end + 
       case when model_id = $model then 1 else 0 end + 
       case when variant_id = $variant then 1 else 0 end + 
       case when engine_id = $engine then 1 else 0 end + 
       case when issueyear_id = $issueyear then 1 else 0 end as matches 
  from master 
 where make_id = $make 
    or model_id = $model
    or variant_id = $variant 
    or engine_id = $engine
    or issueyear_id = $issueyear
 order by matches desc