
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, 4Change and check
Add a row with the same score as another row, then compare the result with and without the secondary ID ordering.
Discussion
Sign in to participate.



