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

So you end up with a query like this:

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!

Advertisements

One thought on “SQL Server: Using RANK to display PERCENT (percentage of relevance)

  1. This is awesome, was looking exactly for something like this so thanks for sharing! However, I was really looking for something along a 100% rank within the result set and down but your formula or more like MS’s rank function returned some odd ranking order. I’d like to share my own solution to my problem by changing SUM(RANK) to MAX(RANK), like this: LOG(RANK, MAX(RANK) OVER( PARTITION BY 1)) and that returned me the rank in the order of 100% and down.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s