Order By Maximum Condition Match

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 conditio

Solution 1:

SELECT *
  FROM (SELECT (CASEWHEN cond1 THEN1ELSE0END +
                CASEWHEN cond2 THEN1ELSE0END +
                CASEWHEN cond2 THEN1ELSE0END +
                ...
                CASEWHEN cond10 THEN1ELSE0END
               ) AS numMatches,
               other_columns...
          FROM mytable
       ) xxx
 WHERE numMatches > 0ORDERBY numMatches DESC

Solution 2:

EDIT: This answer was posted before the question was modified with a concrete example. Marcelo's solution addresses the actual problem. On the other hand, my answer was giving priority to matches of specific fields.

---

You may want to try something like the following, using the same expressions in the ORDER BY clause as in your WHERE clause:

SELECT*FROM      your_table
WHERE     field_1 =100OR
          field_2 =200OR
          field_3 =300ORDERBY  field_1 =100DESC,
          field_2 =200DESC,
          field_3 =300DESC;

I've recently answered a similar question on Stack Overflow which you might be interested in checking out:

• Is there a SQL technique for ordering by matching multiple criteria?

Solution 3:

There are many options/answers possible. Best answer depends on size of the data, non-functional requirements, etc.

That said, what I would do is something like this (easy to read / debug):

Select * from 
    (Select *, iif(condition1 = bla, 1, 0) as match1, ..... , match1+match2...+match10 as totalmatchscore from sourcetable
    where 
      condition1 = bla or
      condition2 = bla2
      ....) as helperquery
    orderby helperquery.totalmatchscore desc

Solution 4:

I could not get this to work for me on Oracle. If using oracle, then this Order by Maximum condition match is a good solution. Utilizes the case when language feature