George Kosmidis

Microsoft MVP | Cloud Solutions Architect | .NET Software Engineer | Organizer of Munich .NET Meetup | Speaker

SQL Server: Using RANK to display PERCENT (percentage of relevance)

by George Kosmidis / Published 9 years and 4 months ago, modified 1 year ago

So you end up with a query l that returns what you searched for and with SQL Server Full Text Search engine, and with it the RANK column! How do you end up with a percentage of relevance for your users to enjoy?


Take a look at the following query for example:

SELECT *
FROM table AS FT_TBL INNER JOIN
   CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 0
ORDER BY KEY_TBL.RANK DESC

That yields something like this:

ID ... KEY RANK
5  ...  5  765
2  ...  2  322
3  ...  5  128
1  ...  1   75

And you are missing a percentage column. Well, not any more!

SELECT *, LOG(RANK) / LOG(SUM(RANK) OVER( PARTITION BY 1)) AS [PERCENT]
FROM table AS FT_TBL INNER JOIN
   CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY]
WHERE KEY_TBL.RANK > 0
ORDER BY KEY_TBL.RANK DESC

* SQL SERVER 2012 now supports LOG ( float_expression [, base ] ). This means you do it this way: LOG(RANK, SUM(RANK) OVER( PARTITION BY 1))

Thanks Alex!

This page is open source. Noticed a typo? Or something unclear?
Edit Page Create Issue Discuss
Microsoft MVP - George Kosmidis

My next events!

Bernhard-Nocht-Straße 97, D-20359, Hamburg