How to find best match across multiple optional properties

Question

Suppose we have a table with different columns representing properties of an object e.g. Colour, Size, Model... many more. How can we get the best matching row if we query for Colour= red, Size= M and Model = x. I want to get the row which matches most of the criteria specified so possibly

1. Red,S,null
2. Red,null, null
3. Red, M, null

Here I should get the 3rd row.

Answer 1

You could just create a SQL CASE expression that returns 0 or 1 (or even fractional values if you're really picky) and sum over the values for the columns in each record, and then apply max to that pseudo-column.