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.

search_term
keyword_match_score.sql
Replay: real traced execution (multi-file project)
CREATE TABLE search_choice AS WITH params(search_term) AS (VALUES ('sql')) 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 ('rust')) 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;

  1. choice ← 1 row

    1CREATE TABLE search_choice AS WITH params(search_term) AS (VALUES ('sql')) SELECT search_term FROM params;2CREATE TABLE articles (id INTEGER, title TEXT, body TEXT);
    values this step1 rowchoice

  2. articles ← 4 rows

    2CREATE TABLE articles (id INTEGER, title TEXT, body TEXT);3INSERT 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');4SELECT 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;
    values this step4 rowsarticles

  3. result ← 2 rows

    3INSERT 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');4SELECT 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;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE search_choice AS WITH params(search_term) AS (VALUES ('rust')) SELECT search_term FROM params;2CREATE TABLE articles (id INTEGER, title TEXT, body TEXT);
    values this step1 rowchoice

  2. articles ← 4 rows

    2CREATE TABLE articles (id INTEGER, title TEXT, body TEXT);3INSERT 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');4SELECT 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;
    values this step4 rowsarticles

  3. result ← 2 rows

    3INSERT 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');4SELECT 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;
    values this step2 rowsresult
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.