# How do I uniquely identify one delivery in ball-by-ball cricket data?

**Use `match_id`, `innings`, `over_no` and `delivery_in_over` together.** The two columns that look like they would do the job both fail, and both fail quietly.

- **The original `ball` column packs two numbers into one decimal.** The tenth delivery of an over reads 1.10, which as a number is the same value as 1.1, the first delivery. Sort by it inside a long over and the tenth ball comes out in the wrong place. It is fine for everything except ordering within an over.

- **`actual_delivery` counts only legal deliveries.** So a wide shares its number with the ball that eventually counts, and it is a sequence rather than a key.

**The general trap, worth carrying to any dataset:** a decimal that encodes two integers is not safely orderable, and a counter that skips is not a key. Both look numeric and behave correctly on small samples, which is why they survive a spot check and break on a long over or a messy innings.

`over_no` and `delivery_in_over` are plain whole numbers added on top of the source data for exactly this reason. Three ball-by-ball tables, one per format, plus `match_info` with venue, dates, teams, toss, result and officials, all joined on `match_id`.

You can query it live or download it. Live SQL and the MCP server: [the free cricket SQL endpoint](https://www.tigzig.com/agents-faq/is-there-a-free-cricket-database-i-can-query-with-sql). Bulk files are one table at a time in Parquet or CSV, or the whole database as a single DuckDB or SQLite file, refreshed twice a day. Column list and field definitions: [db-mcp.tigzig.com/redoc](https://db-mcp.tigzig.com/redoc).

**Credit, because it is a condition of use:** the data comes from Cricsheet ([cricsheet.org](https://cricsheet.org)) under the Open Data Commons Attribution License 1.0, ODC-BY ([opendatacommons.org/licenses/by/1-0/](https://opendatacommons.org/licenses/by/1-0/)). If you publish anything built on it, credit Cricsheet and make the licence clear. Full write-up: [https://www.tigzig.com/post/cricket-full-data-download-live-aug2026](https://www.tigzig.com/post/cricket-full-data-download-live-aug2026).

---
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-identify-one-delivery-in-ball-by-ball-cricket-data
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