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 SELECT id, paragraph,4 MATCH(TABLE vecs) AGAINST(@ft_q) AS SCORE5 FROM vecs6 WHERE MATCH(TABLE vecs) AGAINST(@ft_q)7 ORDER BY SCORE desc8 LIMIT 2009),10vs AS ( /* Find top vector search matches. <*> is dot product. */11 SELECT id, paragraph, v <*> @v_mario_kart AS SCORE12 FROM vecs13 ORDER BY score DESC14 LIMIT 20015),16fts_ranked as (17 select *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) AS fts_rank18 from fts19),20vs_ranked as (21 select *, ROW_NUMBER() OVER (ORDER BY SCORE DESC) as vs_rank22 from vs23)24SELECT *, 0.7 * (1.0 / (NVL(fts_rank,1000.0)+60))25 + 0.3 * (1.0 / (NVL(vs_rank,1000.0)+60) ) as combined_score26FROM fts_ranked f FULL OUTER JOIN vs_ranked v ON f.id = v.id27ORDER BY combined_score desc28LIMIT 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.
