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

JavaScript: Sort Array of Numbers

Tutorial | 0 Comments

MIME Types of Microsoft Office File Formats

Info | 0 Comments

List of all previous European Football Champions and Runners-Up

Info | 0 Comments

MySQL: Change minimum word length for full text search

Tip | 1 Comment

Movies awarded with the most Oscars

Info | 0 Comments

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

Tutorial | 2 Comments

MySQL: CSV Export as File stored on the Server

Tutorial | 0 Comments

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.