SQL SELECT FREETEXT order by Rank

Question

I have this query in SQL Server 2008

SELECT TOP 1000 * 
FROM Quotes 
INNER JOIN QuoteImages ON Quotes.Id = QuoteImages.QuoteId 
WHERE FREETEXT(QuoteText,'some text')

How can I order the results by most relevant or highest rank ?

I have read the msdn documentation, but it seems complicated and I don't know how to create complex stored procedures.

You need to have a look at [`FREETEXTTABLE`](http://msdn.microsoft.com/en-us/library/ms177652.aspx) which returns not only matches, but also information about how relevant each match is — marc_s, Jul 01 '13 at 19:18
I don't agree we this being off topic. How can the OP know about FREETEXTTABLE if he simply doesn't know.... clearly the OP does have some understanding of Transact-SQL and stored procedures... thus the whole , people ask questions on SO .... — Spacemonkey, Aug 15 '16 at 19:53

score 14 · Accepted Answer · answered Jul 01 '13 at 19:20

14

You should use FREETEXTTABLE (link) instead of FREETEXT:

SELECT TOP 1000 Q.*, QI.*
FROM Quotes Q
INNER JOIN QuoteImages QI
    ON Q.Id = QI.QuoteId 
INNER JOIN FREETEXTTABLE(Quotes,QuoteText,'some text') FT
    ON Q.Id = FT.[Key]
ORDER BY RANK DESC

answered Jul 01 '13 at 19:20

Lamak

69,480
12
108
116

SQL SELECT FREETEXT order by Rank

1 Answers1