title: Sorting results by relevance
body: <p>I'm working on an internal search engine for my website. I have an "advanced search" with around 20 criteria, and I would like to sort the results by relevance : the entries meeting the highest number of criteria should show up first. After doing some research, I found that the "CASE WHEN" syntax would be useful, but I'm not sure if I'm using it well. Now I have something like :</p>
  
  <pre><code>select
  case when name like "%test%" then 1 else 0 +
  case when lastname like "%test%" then 1 else 0 end as nb_criteria
  from MyTable1, MyTable2....
  </code></pre>
  
  <p>This works fine. My first column is "nb_criteria" and I just have to order the results accordingly. The problem with that is it just reorders all the entries of my database, so the less relevant results have a value of "0", but they are still there. Therefore I have to use a WHERE statement, which I think would make the server performs the same task twice.</p>
  
  <p>What is the best way to achieve this?</p>
  
  <p>Thanks</p>