tags-table has got three columns web, tags, rank
this table has got FULL TEXT SEARCH enabled
I am able to search by tags and get list of websites by this query
SELECT * FROM tags-table WHERE MATCH (tags) AGAINST ('search,searchengine,search,web' IN BOOLEAN MODE)
but I want order the result by its rank. how do I do it??
I have tried this too but it gives something else.
SELECT * FROM tags-table WHERE MATCH (tags) AGAINST ('search,searchengine,search,web' IN BOOLEAN MODE) ORDER BY ABS(rank)
by the by the rank column is filled with rank for respective sites. for example google has got rank 1, facebook hase got rank2, and yahoo has got rank 4 like that for remaining sites.
tags-table
web | tags | rank
-----------------------------------------------------------------------------------
google.com |search,google,searchengine,engine,web | 1
facebook.com |facebook,social,networking,friends,community | 2
youtube.com |video,youtube,videos,entertainment,media | 3
yahoo.com |yahoo,search,email,news,searchengine | 4
There is no magic column rank. But you can compute the relevance (or rank as you call it) like this:
SELECT
*,
MATCH (tags) AGAINST ('search searchengine search web') AS relevance
FROM
tags-table
WHERE
MATCH (tags) AGAINST ('+search +searchengine +search +web' IN BOOLEAN MODE)
ORDER BY
relevance DESC
Note that the first MATCH AGAINST is not run IN BOOLEAN MODE. That is because IN BOOLEAN MODE almost always returns 1.