mysql - SQL : find rows and sort according to number of matching columns? -

let's imagine having "cars" table such simple structure...

car_id int color enum('black','white','blue') weight enum('light','medium','heavy') type enum('van','sedan','limo') 

fist, i'm selecting car (1, black, heavy, limo), i'd list of related cars sorted number of matching columns (without column weight). so, first i'm expecting see (black, heavy, limo) cars, i'm expecting see cars 2 matching fields etc.

is possible execute kind of sorting using sql?

sorry english, hope question clear you.

thank you.

there few ways optimise sub-queries, without using case statements or sub-optimal join clauses:

select         *             (             select                     selection.carid,                     selection.colour,                     selection.weight,                     selection.type,                     3 relevance                                     tblcars selection                                     selection.colour = 'black' , selection.weight = 'light' , selection.type = 'van'             union             select                     cars.carid,                     cars.colour,                     cars.weight,                     cars.type,                     count(*) relevance                                     tblcars cars                 inner join                     (                         select                                 bycolour.carid                                                             tblcars cars                             inner join                                 tblcars bycolour                             on                                 cars.colour = bycolour.colour                                                             cars.colour = 'black' , cars.weight = 'light' , cars.type = 'van'                                 ,                                 bycolour.carid <> cars.carid                         union                         select                                 byweight.carid                                                             tblcars cars                             inner join                                 tblcars byweight                             on                                 cars.weight = byweight.weight                                                             cars.colour = 'black' , cars.weight = 'light' , cars.type = 'van'                                 ,                                 byweight.carid <> cars.carid                         union                         select                                 bytype.carid                                                             tblcars cars                             inner join                                 tblcars bytype                             on                                 cars.type = bytype.type                                                             cars.colour = 'black' , cars.weight = 'light' , cars.type = 'van'                                 ,                                 bytype.carid <> cars.carid                     ) matches                 on                     cars.carid = matches.carid                 group                     cars.carid,                     cars.colour,                     cars.weight,                     cars.type         ) results     order         relevance desc 

output:

carid   colour  weight  type    relevance 1       black   light   van     3 3       white   light   van     2 4       blue    light   van     2 5       black   medium  van     2 6       white   medium  van     1 7       blue    medium  van     1 8       black   heavy   limo    1