views:

339

answers:

2

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

+1  A: 

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.

Godeke
Can't we sort the records on the basis of relevancy with searched-keyword ??
Prashant
I added a link to a prior question that shows using Rank.
Godeke
+1  A: 

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#

MarlonRibunal