Many developers today are building AI applications like agents or Retrieval-Augmented Generation (RAG) systems that search for information, take the top-ranked items retrieved and use those results.
It's often the case that there may be more than one criteria for ranking. For example, you may be able to use vector similarity search and full-text search on the same set of text chunks.
It's often a good idea to blend the semantic ranking you get from vector search with a more syntactic ranking obtained from text search. This may be appropriate if, for example, you feel it's highly significant if a certain word or phrase appears in a text chunk — but still want meaning-based search accounted for in results.
A popular approach to blend results from two or more different rankings is reciprocal rank fusion (RRF). This tends to be superior to ad-hoc ranking methods like simply adding a full-text score to a vector dot product score, because it relies on ranking (which is a normalized form of relevance information) not dependent on any particular score magnitude or range.
RRF works for the case when you have N different ranked lists and ri is the rank of an item in list i, by calculating the score of each item as:
.png?width=1024&disable=upscale&auto=webp)
where k is a smoothing factor. Using a smoothing factor for RRF prevents giving disproportionate weight to items ranked no.1 (weight = 1.0) compared to no.2 (weight = 0.5) — a 2x difference. With smoothing factor k set to 60 (a value that research shows tends to give good results), rank no.1 gets weight 1/61 ≈ 0.0164 while rank no.2 gets 1/62 ≈ 0.0161, creating a much more gradual decline in weights.
You can further refine RRF by weighting each ranking differently, e.g. with two lists you could weight list 1 as 0.7 and list 2 as 0.3.
Implementing RRF in SQL
Several SQL implementations today have both vector search and full-text search capabilities — so you can query these systems to get vector and full-text search results in rank order. Given the power of SQL, it's straightforward to combine these rankings with RRF, all in a single query. The benefit of this is your SQL system can do the final ranking for you, without the need to import multiple ranked results into your application and combine them there. So it can save you code and development time. Why think and work hard when the query processor can do it for you?
The following example SQL query works in SingleStore to do hybrid search that uses RRF to combine weightings from vector search and full-text search:
1set @ft_q = 'paragraph:"super mario kart"';2WITH fts AS ( /* Find top full-text matches. */3 SELECT4 id,5 paragraph,6 MATCH(TABLE vecs) AGAINST(@ft_q) AS SCORE7 FROM vecs8 WHERE MATCH(TABLE vecs) AGAINST(@ft_q)9 ORDER BY SCORE desc10 LIMIT 20011),12vs AS ( /* Find top vector search matches. <*> is dot product. */13 SELECT14 id,15 paragraph,16 v <*> @v_mario_kart AS SCORE17 FROM vecs18 ORDER BY score DESC19 LIMIT 20020),21fts_ranked as (22 SELECT23 *,24 ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS fts_rank25 FROM fts26),27vs_ranked as (28 SELECT29 *,30 ROW_NUMBER() OVER (ORDER BY SCORE DESC) as vs_rank31 FROM vs32)33SELECT34 *,35 0.7 * (1.0 / (NVL(fts_rank, 1000.0) + 60)) + 0.3 * (1.0 / (NVL(vs_rank, 1000.0) + 60)) as combined_score36FROM fts_ranked f FULL OUTER JOIN vs_ranked v ON f.id = v.id37ORDER BY combined_score desc38LIMIT 5;
Let's examine this query.
It's important the two ranked lists are computed as separate common table expressions (CTEs), and both of them use indexed search. The vector search block uses approximate nearest neighbor (ANN) indexing. The text search block uses inverted index search based on Java Lucene — the same text search indexing system used on Elastic, OpenSearch and Solr.
The rankings are produced in subsequent CTEs that use the ROW_NUMBER window function standard in most modern SQL systems. Pushing the ROW_NUMBER calculation into the vector and text search CTEs prevents the ability to use indexed search for them, making the whole query slow. That's why additional CTEs are used for producing the row numbers for the two search result sets.
The final query block does a FULL OUTER JOIN to make sure rows for items that appear in one ranked list but not the other are still accounted for. Standard INNER JOIN would discard items that were not in both lists.
This query uses a smoothing factor of 60 and weights full-text results at 0.7 and vector search results at 0.3. NULL ranks — which can arise from the outer join — are given an artificially high rank of 1000 so they don't contribute meaningfully to the score.
To get the full context for this example, including the data set and the setup for variable @v_mario_cart, see the SingleStore documentation on hybrid search. Here are the results when run against the sample database from our documentation:
.png?width=1024&disable=upscale&auto=webp)
Notice that values with high ranks rise to the top (as with the (2,1) rank pair), but a poor ranking on one sorted input doesn't prevent an item from participating in the results at a high level, e.g., the (3,11) pair ranks 3 overall, better than (5,8) which ranks at 4.
In summary, you can use the power of SQL, specifically CTEs, the ROW_NUMBER window function and general expressions to perform powerful hybrid search rankings. We've illustrated how this can be done for RRF. If your SQL implementation supports high-level language user-defined functions (UDFs) (like SingleStore's external functions or Wasm UDFs), you can also create functions and queries to implement late interaction models, cross-encoders or other reranking techniques. Full-text search and vector search are a great foundation to build on to feed into reranking to produce what are ultimately more meaningful search results.
