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

0 0 votes
Article Rating

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:

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!

* 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!

0 0 votes
Article Rating
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Tony Phan

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.

Tony Phan

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.