0

I'm performing semantics analysis on 1000 docs. I would like to the top 5 matches for each of the doc. here goes the query that I'm using;

DECLARE @FTE hierarchyid
SELECT @FTE = path_locator
   from dbo.LatinBooks

Select top (5)
 e.name
,d.name as MatchedFTE
,KEY_TBL.score
FROM SEMANTICSIMILARITYTABLE
     (dbo.LatinBooks,
      file_stream,
      @FTE ) as KEY_TBL
 INNER JOIN dbo.LatinBooks d on KEY_TBL.matched_document_key= d.path_locator
 left Join (select path_locator,name from dbo.LatinBooks) e on @fte=e.path_locator 
Order by Key_tbl.score DESC;

This query works , it returns the top matches for the first doc. What Changes can i do to the script that it will return the matches for all 1000 docs. The results will show 5,000 rows ( One Doc and Five matches)

Gjorge
  • 1
  • 3

1 Answers1

0

You want row_number():

select *
from (Select e.name, d.name as MatchedFTE, KEY_TBL.score,
             row_number() over (partition by e.name order by KEY_TBL.score desc) as seqnum
      from SEMANTICSIMILARITYTABLE(dbo.LatinBooks,file_stream, @FTE
                                  ) KEY_TBL INNER JOIN
           dbo.LatinBooks d
           on KEY_TBL.matched_document_key = d.path_locator left Join
           dbo.LatinBooks e
           on @fte = e.path_locator 
     ) x
where seqnum <= 5;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • When I run the script as you sent it, It asks to declare the '@FTE'. If declare '@FTE' , it will return the top (5) for the first document ONLY. I need matches for all docs – Gjorge Jun 30 '16 at 20:35
  • @Gjorge . . . You have `@FTE` in your original query. – Gordon Linoff Jul 01 '16 at 02:57
  • Yes i do have @fte in the original query – Gjorge Jul 05 '16 at 16:01
  • @Gjorge . . . The `partition by` needs to be by the column used to identify a document. I figured the `name` was the right field, but I really don't know. You can try it with other columns. – Gordon Linoff Jul 06 '16 at 01:46