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.

max_rank
ranked_search_page.sql
Replay: real traced execution (multi-file project)
CREATE TABLE search_choice AS WITH params(max_rank) AS (VALUES (2)) 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 (1)) 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 (3)) 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;

  1. choice ← 1 row

    1CREATE TABLE search_choice AS WITH params(max_rank) AS (VALUES (2)) SELECT max_rank FROM params;2CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);
    values this step1 rowchoice

  2. documents ← 4 rows

    2CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);3INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);4WITH 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;
    values this step4 rowsdocuments

  3. result ← 2 rows

    3INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);4WITH 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;
    values this step2 rowsresult

  1. choice ← 1 row

    1CREATE TABLE search_choice AS WITH params(max_rank) AS (VALUES (1)) SELECT max_rank FROM params;2CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);
    values this step1 rowchoice

  2. documents ← 4 rows

    2CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);3INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);4WITH 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;
    values this step4 rowsdocuments

  3. result ← 1 row

    3INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);4WITH 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;
    values this step1 rowresult

  1. choice ← 1 row

    1CREATE TABLE search_choice AS WITH params(max_rank) AS (VALUES (3)) SELECT max_rank FROM params;2CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);
    values this step1 rowchoice

  2. documents ← 4 rows

    2CREATE TABLE documents (id INTEGER, title TEXT, clicks INTEGER, freshness INTEGER);3INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);4WITH 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;
    values this step4 rowsdocuments

  3. result ← 3 rows

    3INSERT INTO documents VALUES (1, 'SQL tutorial', 30, 5), (2, 'SQL joins guide', 20, 9), (3, 'SQLite windows', 15, 7), (4, 'CSS layout', 40, 2);4WITH 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;
    values this step3 rowsresult
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.