mysql - 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

Not overly efficient, but...

SELECT
  exact.car_id   AS e_car_id, exact.color   AS e_color, 
  exact.weight   AS e_weight, exact.type    AS e_type,
  related.car_id AS r_car_id, related.color AS r_color, 
  related.weight AS r_weight, related.type  AS r_type,
  CASE WHEN related.color = exact.color      THEN 1 ELSE 0 END
   + CASE WHEN related.weight = exact.weight THEN 1 ELSE 0 END
   + CASE WHEN related.type = exact.type     THEN 1 ELSE 0 END
  AS rank
FROM
  cars AS exact
  INNER JOIN cars AS related ON (
    related.car_id <> exact.car_id 
    AND CASE WHEN related.color = exact.color   THEN 1 ELSE 0 END
      + CASE WHEN related.weight = exact.weight THEN 1 ELSE 0 END
      + CASE WHEN related.type = exact.type     THEN 1 ELSE 0 END
    >= 1
  )
WHERE
  exact.car_id = 1 /* black, heavy, limo */
ORDER BY
  rank DESC

This would not run very fast on large data sets, since neither the JOIN nor the ORDER BY can make use of an index. Very probably a more optimal version exists.

Output on my test setup looks like this:

e_car_id  e_color  e_weight  e_type  r_car_id  r_color  r_weight  r_type  rank
1         black    heavy     limo    7         black    heavy     limo    3
1         black    heavy     limo    2         black    light     limo    2
1         black    heavy     limo    3         black    heavy     van     2
1         black    heavy     limo    4         black    medium    van     1
1         black    heavy     limo    5         blue     light     limo    1