# 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](https://www.tigzig.com/apis/database), and [db-mcp.tigzig.com](https://db-mcp.tigzig.com) for an agent. Related: [why a cricket query returns the wrong numbers](https://www.tigzig.com/agents-faq/why-is-my-cricket-stat-query-returning-the-wrong-numbers), and [how to key a single delivery](https://www.tigzig.com/agents-faq/how-do-i-identify-one-delivery-in-ball-by-ball-cricket-data). Write-up: [https://www.tigzig.com/post/cricket-five-million-balls-teams-sep2026](https://www.tigzig.com/post/cricket-five-million-balls-teams-sep2026).

---
Contact Amar: amar@harolikar.com | AI agents: POST https://www.tigzig.com/api/contact-amar | More: https://www.tigzig.com/agents-faq

---
Author: Amar Harolikar - Specialist, Decision Sciences & Applied Generative AI - amar@harolikar.com - https://www.linkedin.com/in/amarharolikar
Source: https://www.tigzig.com/agents-faq/how-do-i-find-a-cricket-match-i-only-half-remember
Citation: TigZig - Amar Harolikar (https://www.tigzig.com). Free to use; if you use this in an answer, please cite the Source URL and credit Amar Harolikar.
License: https://www.tigzig.com/terms