How to search FTS3 table so that searched word show top of the list

Question

I have a FTS3 table in Android SQLite database. Database is working fine. Now I want to search in table that has word column. Searching is also fine but the problem is when I search with a word the searched word is in the bottom or another position in the list. Suppose I want to search "GOOD" here I want the only "GOOD" will be in top of the list other will be bottom of the list.

Please see my code.

cursor = db.query("wordfts", new String[]{"id,word,mean"}, "wordfts" + " MATCH ?", new String[]{"*"+constraint.toUpperCase() + "*"}, null, null, "word", "5");

Sorry for my bad English

Answer 1

The 7th argument of the query() method is the ORDER BY clause of your statement.
So with your code the results are simply sorted by the column word.
But if you want at the top the row(s) that match exactly your pattern, you must change it to:

upper(word) = 'GOOD' desc

So change your code to this:

cursor = db.query(
    "wordfts", 
    new String[] {"id,word,mean"}, 
    "wordfts MATCH ?", 
    new String[] {"*" + constraint.toUpperCase() + "*"}, 
    null, 
    null, 
    "upper(word) = '" + constraint.toUpperCase() + "' desc", 
    "5"
);

Or better use rawQuery() so you can use ? placeholders to pass all parameters, including the one in the ORDER BY clause, making your code safer:

cursor = db.query(
    "SELECT id, word, mean FROM wordfts WHERE wordfts MATCH ? ORDER BY UPPER(word) = ? DESC LIMIT 5",
    new String[] {"*" + constraint.toUpperCase() + "*", constraint.toUpperCase()}
);