How to display relevant record first using FULLTEXT in SQL ??

Question

I am using the FULLTEXT in my sql query to search the records.

        SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId,
                d.CreatedOn, d.Rank, d.voteCount, d.commentCount, d.viewCount
        FROM Data d 
                INNER JOIN Keyword k
                    ON d.DataId = k.DataId
        WHERE FREETEXT(k.Keyword, @SearchQ) AND d.CategoryId=@CategoryId AND d.IsSearch=1 AND d.IsApproved=1

When I am searching for rockstone hotel, I have a record with the same name in my table but it displays some other record first and then displays "rockstone hotel's" record at 3 position. Why its happening and what are the way's to improve this ???

I'm using SQL Server 2005

Thanks

Answer 1

You have no Order By clause, which frees the database to return records in any order. Usually it is the order in which it encounters them as it processes the where clause, so it is easy to believe there is a "natural" order that the database uses. If you want a specific order you either need to add an Order By clause that will use some scoring method you create or you must order them in the program that receives the records.

(I would have to guess that the other records you are pulling also contain the search criteria, just farther into the text field.)

See here for an example of using Rank to order your records: http://stackoverflow.com/questions/53538/mssql-freetext-match-how-do-i-sort-by-relevance The Rank column is generated by the text matching call.

Answer 2

Quoted from MSDN (SQL Server Developer Center):

Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word and will be discarded.

http://msdn.microsoft.com/en-us/library/ms176078(SQL.90).aspx#