CODEWITHSUNDEEP
sqlselectsql-order-bywhere-clause
Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

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 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.

Upvotes: 9

Views: 2925

Answers (4)

Rohit Gupta
Rohit Gupta

Reputation: 33

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

Upvotes: 0

Daniel Vassallo
Daniel Vassallo

Reputation: 344371

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 = 100 OR
          field_2 = 200 OR
          field_3 = 300
ORDER BY  field_1 = 100 DESC,
          field_2 = 200 DESC,
          field_3 = 300 DESC;

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

Upvotes: 2

Tobiasopdenbrouw
Tobiasopdenbrouw

Reputation: 14039

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
    order by helperquery.totalmatchscore desc

Upvotes: 0

Marcelo Cantos
Marcelo Cantos

Reputation: 185902

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

Upvotes: 16

Related Questions