Wikis & learning

Filter and order the rows for a small results page

An explicit filter determines which records qualify. An explicit ordering determines which qualifying record appears first. Both are necessary for a reproducible short results page.

An explicit filter determines which records qualify. An explicit ordering determines which qualifying record appears first. Both are necessary for a reproducible short results page.

Walkthrough

The query binds the topic and uses an ID as the tie-breaker for equal scores. LIMIT only truncates the final ordering. On a larger table, index the columns used for filtering and sorting.

PHP example

<?php
$db = new PDO('sqlite::memory:');
$db->exec("CREATE TABLE lessons(id INTEGER PRIMARY KEY, topic TEXT, score INTEGER);
INSERT INTO lessons VALUES(1,'data',3),(2,'web',9),(3,'data',5),(4,'data',5)");
$get = $db->prepare('SELECT id FROM lessons WHERE topic=? ORDER BY score DESC,id ASC LIMIT 2');
$get->execute(['data']);
echo implode(', ', $get->fetchAll(PDO::FETCH_COLUMN));

Expected output

3, 4

Change and check

Add a row with the same score as another row, then compare the result with and without the secondary ID ordering.