Selecting the most specific match from one table
SELECT *
FROM dogs
ORDER BY CASE WHEN breed &lt;=&gt; @breed THEN @breed_weight ELSE 0 END
        +CASE WHEN color &lt;=&gt; @color THEN @color_weight ELSE 0 END
        +CASE WHEN skill &lt;=&gt; @skill THEN @skill_weight ELSE 0 END DESC LIMIT 1


The parameter weighting factors can be constant literals or varying query parameters.


find by ('American Bulldog',  'Black', 'Runs fast')
Returns id 5



@color_weight must be greater than @skill_weight. Backward record id=1 will be returned, and a random of them if weights are equal.

If some search condition is mandatory - move the comparison to WHERE clause and remove it from ORDER BY expression.


  Yeah the breed and color can basically be in the where clause, they will be mandatory.


SELECT *
FROM dogs
WHERE breed = @breed
  AND color = @color
ORDER BY (skill &lt;=&gt; @skill) DESC LIMIT 1