Is this possible in MySQL?

[Anon]

I'm trying to a select in MySQL with a where clause that can have many fields in it. ie:

select username where eyes = 'blue' and hair = 'black' and favorite_color = 'green'

I would like to have MySQL return a result set which instead of matching all the fields in the where clause ONLY, it gives me back rows ordered by how many of those fields it was able to match. ie:

Instead of only returning one result: Mary, who has blue eyes, black hair and green as a favorite color, it would return:

Mary, who matches all three,
Jane, who matches only two,
and Bob, who only matches ony one.

Thanks in advance, Mark.

[Anon]

select username,
case when eyes = 'blue' then 1 else 0 end +
case when hair = 'black' then 1 else 0 end +
case when favorite_color = 'green' then 1
else 0 end as properties
from members
where eyes = 'blue'
or hair = 'black'
or favorite_color = 'green'
order by properties desc

[Anon]

Many thanks! 🙂

[mikesgt2]

That is very clever, you learn something new every day.