How can I select top 10 records from mytable AND THEN do a freetexttable query on only those 10 records

Question

Post reply

How can I select top 10 records from mytable AND THEN do a freetexttable query on only those 10 records

nik.bhagat

SSC Rookie

Points: 35
More actions
September 17, 2009 at 11:26 am

#209948

Hello - I'm really hoping someone can help me out with this.
I have this FREETEXTABLE Query (below) that works perfectly... BUT I want results/records to return ONLY if they belong to the top 10 newest records. That is, I don't want to use top_n_rank for this because I do not want the top 10 'most relevant' results, but rather I want to know if the searchstring (@searchstring) returns anything from the 10 NEWEST/LATEST records added to the database..
ANY IDEAS? PLEEEASE help!!
QUERY:
SELECT Documents.DocID,
Documents.Category,
Documents.Publication,
Documents.Volume,
Documents.Edition,
Documents.ItemTitle,
Documents.Author,
Documents.Engraver,
Documents.Keywords,
Documents.Summary,
Documents.AttachmentName,
Documents.AttachmentType,
Documents.ImageName,
Documents.ImageMedium,
Documents.ImageSize,
Documents.XMLFileName,
Documents.HTMLFileName,
KEY_TBL.RANK
FROM Documents AS Documents INNER JOIN
FREETEXTTABLE(Documents, *,
@searchstring) AS KEY_TBL
ON Documents.DocID = KEY_TBL. WHERE ((@Category IS NULL) OR (Documents.Category = @Category)) order by KEY_TBL.RANK desc
PS: Simply using 'Select TOP(10)..." doesn't work at all... 🙁

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

arun.sas

SSChampion

Points: 11831

September 17, 2009 at 9:45 pm

#1055350

SET ROWCOUNT 10

SELECT * FROM MYTABLE

nik.bhagat SSC Rookie Points: 35 More actions · Answer 2

soo...

would I just replace the '*' in SELECT * FROM mytable with the fulltextable query??

arun.sas SSChampion Points: 11831 More actions · Answer 3

Apply this option with reference of the BOL.

http://technet.microsoft.com/en-us/library/ms188774.aspx

nik.bhagat SSC Rookie Points: 35 More actions · Answer 4

Hm. Thanks for the suggestion, but that solution just stops the query after N rows have been processed. That means the query will run and find any 10 rows that are most relevant according to the FREETEXTTABLE query... and then stop. This isn't what I'm looking for. I need the top 10 records to be selected first (ie the last ten records to be added to the database) and then have the FREETEXTTABLE query run on only those 10 records..

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

What about using a subquery. I have'nt given the full query and this is just a guess...

SELECT SELECT Documents.DocID,

Documents.Category,

Documents.Publication,

Documents.Volume,

Documents.Edition,

Documents.ItemTitle,

Documents.Author,

Documents.Engraver,

Documents.Keywords,

Documents.Summary,

Documents.AttachmentName,

Documents.AttachmentType,

Documents.ImageName,

Documents.ImageMedium,

Documents.ImageSize,

Documents.XMLFileName,

Documents.HTMLFileName,

KEY_TBL.RANK FROM

(SELECT Top 10 SELECT Documents.DocID,