How do I find a cricket match when I only half remember it?

Filter on the thing you remember, which is almost always an aggregate. You rarely remember a match id. You remember a score, a partnership, an over that went wrong. So group the deliveries, put a tolerance band in the HAVING clause, and join to the match table for the date, venue and result. Start wide and narrow.

A worked one. Someone made about 98 off about 60 balls and you cannot place the game:

SELECT b.match_id, m.start_date, m.venue, b.striker,
       SUM(b.runs_off_bat) AS runs,
       COUNT(*) FILTER (WHERE b.wides IS NULL) AS balls_faced
FROM ball_by_ball b JOIN match_info m USING (match_id)
GROUP BY 1,2,3,4
HAVING SUM(b.runs_off_bat) BETWEEN 96 AND 100
   AND COUNT(*) FILTER (WHERE b.wides IS NULL) BETWEEN 58 AND 62
ORDER BY m.start_date DESC

Four things break this quietly, and every one returns a plausible answer.

• Balls faced is not a row count. A wide is not a ball faced, so filter it out. And the extras columns are NULL where nothing was conceded, never zero, so WHERE wides = 0 matches nothing at all - measured, it selects 0 of 5,047,757 rows. The query still runs and hands you an empty result, which reads as "that innings does not exist".
• A partnership is two rows per pair. Strike rotates, so the same two batters appear as (A,B) and (B,A) and every stand is split in half. Normalise the pair with LEAST(striker, non_striker) and GREATEST(...) before you group, or a 449-run stand never shows up.
• Six of the twelve dismissal types are not the bowler's wicket. Credited: caught, bowled, lbw, caught and bowled, stumped, hit wicket. Not credited: run out, obstructing the field, hit the ball twice, and the three retirements. Count wicket_type IS NOT NULL and a bowler picks up other people's work.
• Balls remaining in a chase needs the scheduled length, and a rain rule will wreck it. Use target_overs where it is set and fall back to overs, and exclude matches with a result method or a non-standard outcome. Without that filter an abandoned T20 comes back as 209 balls remaining.

The general shape. This is a tolerance problem. Widen the band until something comes back, sort by date, and add a second remembered detail as a filter rather than tightening the first one.

Both engines take this SQL and the whole database is downloadable if you would rather work locally: https://www.tigzig.com/apis/database, and db-mcp.tigzig.com for an agent. Related: why a cricket query returns the wrong numbers, and how to key a single delivery. Write-up: https://www.tigzig.com/post/cricket-five-million-balls-teams-sep2026.