RANK BY Clause
The results from a query include both the rows returned by the query and the rank column, if included in the SELECT clause. The rank values are calculated by the Search engine, and are returned as integers in the range zero to 1000. To make the rank results more meaningful, the query can control how ranks are calculated, and can also affect the returned rank values. Both operations are performed in the RANK BY clause. The syntax for the RANK BY clause is:
… WHERE ( <search_condition> ) RANK BY [ ( ] <rank_specification> [ ) ]
The RANK BY clause is applied to the search_condition immediately preceding it. The parentheses surrounding the search_condition are required. The parentheses surrounding the rank_specification are optional.
More than one RANK BY clause can be applied to a single condition. Include additional RANK BY clauses one after the other using parentheses.
Note
Full-text predicates return rank values in the range 0 to 1000. Rank values for all documents matched by a non-full-text predicate are 1000. Modifications to the rank values should take this into account.
There are two types of rank_specifications: WEIGHT and COERCION. The WEIGHT function applies a multiplier to the rank value for the returned row. The COERCION function can be used to multiply, add, or set a specific rank value for the row.
Each rank_specification can include either zero or one WEIGHT function. Zero or more COERCION functions can also be included. If both WEIGHT and COERCION functions are included in a RANK BY clause, the WEIGHT function must be first.
WEIGHT Function
The syntax of the WEIGHT function is:
… WEIGHT ( <weight_multipler> )
Note
The CONTAINS and FREETEXT predicate column weighting features support a shorthand format using a colon. The RANK BY clause does not support the shortened form.
The multiplier must be a decimal fraction value from 0.001 to 1.000. The weight value is multiplied by the value returned by the search_condition predicate.
The following example WHERE clause uses the WEIGHT function to move documents having the word "computer" in the title higher than those having it the Content field:
… WHERE CONTAINS ( "urn:schemas.microsoft.com.fulltextqueryinfo:contents",'computer' ) RANK BY WEIGHT ( 0.25 )ORCONTAINS( "urn:schemas-microsoft-com:office:office#Title",'computer' ) RANK BY WEIGHT ( 0.90 )
There is a limitation when using RANK BY WEIGHT, it will not work with CONTAINS clauses that use Boolean conditions, for example:
… CONTAINS
( "urn:schemas.microsoft.com.fulltextqueryinfo:contents", '
"computer" and "software" ' )
COERCION Function
The rank coercion function can be used to change the returned rank value by adding, multiplying, or configuring it to a specific value. The syntax of the COERCION function is:
… COERCION ( <coercion_operation> , <coercion_value> )
The coercion value must be an integer value, or a decimal value between 0 and 1. If a decimal value is supplied, only three digits of precision are allowed.
The following table describes the available coercion_operation settings.
| Coercion operation | Description |
|---|---|
| ABSOLUTE | The rank value returned is the value specified in the coercion_value. The value must be from 0 to 1000. |
| ADD | The rank value returned is the sum of the original rank value and the specified coercion_value. The value must be from 0.001 to 1.0. |
| MULTIPLY | The rank value returned is the product of the original rank value and the specified coercion_value. The value must be from 0.001 to 1.0. |
Important
Search can return rank values only in the range of 0 to 1000.
Examples
The following example uses the COERCION function to set all documents with "computer" in the title to have a rank of 1000, while reducing by one-half the rank of documents containing both "computer" and "software" in the title.
… WHERE CONTAINS ( "urn:schemas-microsoft-com:office:office#Title" ,'computer' )RANK BY COERCION ( ABSOLUTE , 1000 )OR CONTAINS ( "urn:schemas-microsoft-com:office:office#Title" ,'"computer" AND "software"' )RANK BY COERCION ( MULTIPLY, 0.500 )