SQL Server Freetext match - how do I sort by relevance

Question

Is it possible to order results in SQL Server 2005 by the relevance of a freetext match? In MySQL you can use the (roughly equivalent) MATCH function in the ORDER BY section, but I haven't found any equivalence in SQL Server.

From the MySQL docs:

For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

So for example you could order by the number of votes, then this relevance, and finally by a creation date. Is this something that can be done, or am I stuck with just returning the matching values and not having this ordering ability?

Answer 1

If you are using FREETEXTTABLE then it returns a column name Rank, so order by Rank should work. I don't know if other freetext search methods are also returning this value or not. You can have a try.

Answer 2

Thanks Biri,

I've refactored the query to use FREETEXTTABLE rather than just FREETEXT - with a bit of hassle until I worked out that KEY is a reserved word and needs to be escaped, and all seems good.

Answer 3

Both FREETEXTTABLE and CONTAINSTABLE will return the [RANK] column, but make sure you are using either the correct variation or union both of them to get all appropriate results.