Search and Ranking

Keyword Scores

Rank Matching Rows

A simple search result can compute a score from title and body matches, then sort the strongest rows first.

Program

Play the script to choose a keyword and rank articles by deterministic match score.

keyword_match_score.sql 
CREATE TABLE search_choice AS WITH params(search_term) AS (VALUES ()) SELECT search_term FROM params;
CREATE TABLE articles (id INTEGER, title TEXT, body TEXT);
INSERT INTO articles VALUES (1, 'SQL basics', 'learn select and joins'), (2, 'Rust database guide', 'store rows with sqlx'), (3, 'Search tips', 'rank text with keywords'), (4, 'Rust ownership', 'memory safety guide');
SELECT id, title, (CASE WHEN lower(title) LIKE '%' || (SELECT search_term FROM search_choice) || '%' THEN 2 ELSE 0 END + CASE WHEN lower(body) LIKE '%' || (SELECT search_term FROM search_choice) || '%' THEN 1 ELSE 0 END) AS score FROM articles WHERE lower(title) LIKE '%' || (SELECT search_term FROM search_choice) || '%' OR lower(body) LIKE '%' || (SELECT search_term FROM search_choice) || '%' ORDER BY score DESC, id;

CREATE TABLE search_choice AS WITH params(search_term) AS (VALUES ()) SELECT search_term FROM params;
CREATE TABLE articles (id INTEGER, title TEXT, body TEXT);
INSERT INTO articles VALUES (1, 'SQL basics', 'learn select and joins'), (2, 'Rust database guide', 'store rows with sqlx'), (3, 'Search tips', 'rank text with keywords'), (4, 'Rust ownership', 'memory safety guide');
SELECT id, title, (CASE WHEN lower(title) LIKE '%' || (SELECT search_term FROM search_choice) || '%' THEN 2 ELSE 0 END + CASE WHEN lower(body) LIKE '%' || (SELECT search_term FROM search_choice) || '%' THEN 1 ELSE 0 END) AS score FROM articles WHERE lower(title) LIKE '%' || (SELECT search_term FROM search_choice) || '%' OR lower(body) LIKE '%' || (SELECT search_term FROM search_choice) || '%' ORDER BY score DESC, id;
score The query gives title matches more weight than body matches.
case-insensitive `lower(...) LIKE` keeps the tiny example deterministic without a full-text index.
rank order `ORDER BY score DESC, id` makes ties repeatable.