sql - Order by Maximum condition match

Question

Please help me to create a select query which contains 10 'where' clause and the order should be like that: the results should be displayed in order of most keywords(where conditions) matched down to least matched.

NOTE: all 10 condition are with "OR".

Please help me to create this query. i am using ms-sql server 2005

Like:

Select *
  from employee
 where empid in (1,2,4,332,434)
    or empname like 'raj%'
    or city = 'jodhpur'
    or salary >5000

In above query all those record which matches maximum conditions should be on top and less matching condition record should be at bottom.

Answer 1

SELECT *
  FROM (SELECT (CASE WHEN cond1 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                ...
                CASE WHEN cond10 THEN 1 ELSE 0 END
               ) AS numMatches,
               other_columns...
          FROM mytable
       ) xxx
 WHERE numMatches > 0
 ORDER BY numMatches DESC