How to order SQL results in Full Text Searches-CodePudding

I am trying to use full text search in my project. When I use Natural Language Full-Text Searches with more than word, it works fine for me. Here are some examples of my code:

SELECT * FROM table WHERE MATCH (title, title_ar, title_en) AGAINST ('some exemple' IN NATURAL LANGUAGE MODE);

I can also do this to get score and order it by score:

SELECT *,
    MATCH (title, title_ar, title_en) AGAINST ('some exemple' IN NATURAL LANGUAGE MODE) as scor 
    FROM table 
    WHERE MATCH (title, title_ar, title_en) AGAINST ('some exemple' IN NATURAL LANGUAGE MODE);

The problem is when I use one word for my search like this:

SELECT *,
    MATCH (title, title_ar, title_en) AGAINST ('exemple' IN NATURAL LANGUAGE MODE) as scor 
    FROM table 
    WHERE MATCH (title, title_ar, title_en) AGAINST ('exemple' IN NATURAL LANGUAGE MODE);

It gives me more results with same scores sorted in ascending. For me, I want to order it by id DESC, but I can't do that.

CodePudding user response：

Basically, you want to order the results by scor and id in the case of ties. Untested code that will hopefully solve this for you:

SELECT *,
    MATCH (title, title_ar, title_en) AGAINST ('exemple' IN NATURAL LANGUAGE MODE) as scor 
    FROM table 
    WHERE MATCH (title, title_ar, title_en) AGAINST ('exemple' IN NATURAL LANGUAGE MODE)
ORDER score DESC, id