title: Return rows within a percentage of the maximum value for a column
body: <p>Using SQL Server 2005 Full Text Search I'd like to return values within a % of the maximum relevance result for that search.</p>
  
  <pre><code> SELECT 
   A.ActivityID,
   KEY_TBL.Rank as Relevance, 
   DENSE_RANK() OVER (ORDER BY  Rank DESC) as SearchRank
   FROM Activity A 
   INNER JOIN FREETEXTTABLE(vwActivitySearch, FTS,'My search expression') AS KEY_TBL ON A.ActivityID = KEY_TBL.[KEY]
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>    ActivityID  Relevance   SearchRank
      ----------- ----------- --------------------
      89378       242         1
      89406       242         1
      88083       236         2
      88214       236         2
      84007       197         3
      83434       197         3
      13017       172         4
      89247       164         5
      89346       164         5
  </code></pre>
  
  <p>Rather than return by rank, I'd like to return values that are greater than 90%, or some arbitrary percentage, of the maximum relevance, so in this example</p>
  
  <pre><code> WHERE Relevance&gt;(242*0.9). 
  </code></pre>
  
  <p>I'm sure there's a simple way to achieve this, but I can't see it.</p>
  
  <p>Some constraints - </p>
  
  <ul>
  <li>The query is a CTE expression within a UDF.</li>
  <li>I could easily run an initial query to obtain @MAXRelevance= SELECT MAX(Relevance)... then use Max(Relevance) in a WHERE clause, but full text search does not guarantee to return the same absolute values for relevance results on repeated searches.</li>
  </ul>
  
  <p>Existing function:</p>
  
  <pre><code> CREATE FUNCTION [dbo].[xxActivitySearch] (@SearchTerm varchar(255)='',@ResultDepth int)
   RETURNS @ReturnTable Table (ActivityID int,Relevance int,SearchRank int)
   AS
   BEGIN
   WITH T AS (
   SELECT 
     A.ActivityID,
     KEY_TBL.Rank as Relevance, 
     DENSE_RANK() OVER (ORDER BY  Rank DESC) as SearchRank
   FROM Activity A 
  INNER JOIN FREETEXTTABLE(vwActivitySearch, FTS,@SearchTerm) AS KEY_TBL ON A.ActivityID=KEY_TBL.[KEY])
  INSERT @ReturnTable SELECT * FROM T WHERE (SearchRank&lt;=@ResultDepth)
  RETURN
  END 
  </code></pre>