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

Question

Let's imagine that we are having "cars" table with such a 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), then I'd like to get list of related cars sorted by number of matching columns (without any column weight). So, first I'm expecting to see (black, heavy, limo) cars, then I'm expecting to see cars with only 2 matching fields etc.

Is it possible to execute this kind of sorting using SQL?

Sorry for my English, but I really hope that my question is clear for you.

Thank you.

Answer 1

I know this is an old question, but you should be able to wrap an expression in parenthesis to evaluate it

SELECT   *           
FROM     `cars`
WHERE    `color` = "black"
   OR    `weight` = "heavy"
   OR    `type` = "limo"
ORDER BY (   (`color` = "black")
           + (`weight` = "heavy")
           + (`type` = "limo") 
         ) DESC

Each expression inside parenthesis will equal 1 if true, 0 if false; thus the sum of which will be the number of matches.