11 Vote

MySQL: Sort by Relevance

Question by Progger99 | Last update on 2021-05-04 | Created on 2012-05-13

I want to sort a search query for multiple words according to the relevance, that is: The more my keywords are included in the record, the higher should the record be shown, because it has more relevance.

Up to now, my MySQL query is lookin like that:

SELECT * FROM tab WHERE
  text LIKE 'word1' OR
  text LIKE 'word2' OR
  text LIKE 'word3'
LIMIT 10 ORDER BY ??

But what comes after the ORDER BY? I hope someone can help me. Incidentally, I would not use a full text search!

ReplyPositiveNegative
Avatar Stefan Trost
1Best Answer1 Vote

One way to implement that without using a full text search would be to take a query that looks something like this:

ORDER BY
  CASE WHEN text LIKE 'word1' THEN 1 ELSE 0 END
+ CASE WHEN text LIKE 'word2' THEN 1 ELSE 0 END
+ CASE WHEN text LIKE 'word3' THEN 1 ELSE 0 END
DESC

Thus, each matching word leads to an increase of then rank when sorting. If all three words occur in "text", we get at a rank of 3, while the amount with only one matching word is 1 and therefore, the corresponding record appears more below.
Last update on 2021-05-04 | Created on 2012-05-15

ReplyPositive Negative
Reply

Related Topics

MySQL: Order by multiple Columns

Question | 1 Answer

MySQL: Combine full text search with LIKE search for words with 3 letters

Tutorial | 2 Comments

MySQL: SELECT 0 instead of NULL

Question | 4 Answers

JavaScript: Sort array of numbers

Tutorial | 0 Comments

MySQL: Select random record from table

Tip | 0 Comments

Quicksort: Sort two Columns

Question | 1 Answer

MySQL: Set Initial Value for AUTO INCREMENT

Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.