Search and Ranking

Ranked Pages

Return the Top Results

A search page often ranks all matches first, then keeps only the top rows for display.

Program

Play the script to choose how many ranked search results are returned.

ranked_search_page.sql 
CREATE TABLE search_choice AS WITH params(max_rank) AS (VALUES ()) SELECT max_rank FROM params;
CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);
INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);
WITH ranked AS (SELECT id, title, clicks + freshness AS score, ROW_NUMBER() OVER (ORDER BY clicks + freshness DESC, id) AS result_rank FROM documents WHERE title LIKE 'SQL%' OR title LIKE 'SQLite%') SELECT id, title, score, result_rank FROM ranked WHERE result_rank <= (SELECT max_rank FROM search_choice) ORDER BY result_rank;

CREATE TABLE search_choice AS WITH params(max_rank) AS (VALUES ()) SELECT max_rank FROM params;
CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);
INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);
WITH ranked AS (SELECT id, title, clicks + freshness AS score, ROW_NUMBER() OVER (ORDER BY clicks + freshness DESC, id) AS result_rank FROM documents WHERE title LIKE 'SQL%' OR title LIKE 'SQLite%') SELECT id, title, score, result_rank FROM ranked WHERE result_rank <= (SELECT max_rank FROM search_choice) ORDER BY result_rank;

CREATE TABLE search_choice AS WITH params(max_rank) AS (VALUES ()) SELECT max_rank FROM params;
CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);
INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);
WITH ranked AS (SELECT id, title, clicks + freshness AS score, ROW_NUMBER() OVER (ORDER BY clicks + freshness DESC, id) AS result_rank FROM documents WHERE title LIKE 'SQL%' OR title LIKE 'SQLite%') SELECT id, title, score, result_rank FROM ranked WHERE result_rank <= (SELECT max_rank FROM search_choice) ORDER BY result_rank;
ROW_NUMBER `ROW_NUMBER` assigns a stable display rank after sorting by score.
top results Filtering on the rank keeps only the first search results.
score formula The example score combines fixed popularity and freshness columns.