mcpdata Request an invite

Answers / Analyse data

How do I find duplicate rows in a large table?

Recipe verified on Ran on the public demo (read-only)
Short answer

Count rows per key and look for counts above one. Your AI groups by the id column, counts, and sorts the counts descending: any id with a count of 2 or more is duplicated. On a very large table, check a slice at a time (a month, a load): each query stays small. The demo has a few duplicate orders on purpose.

Steps

  1. Connect your assistant to the public demo. It's a read-only instance with 10 million synthetic orders, so you can try this before you have your own. In Claude Code: 
    claude mcp add --transport http mcpdata-demo https://demo.mcpdata.org/mcp --header "Authorization: Bearer cdb_live_PkVoldus8qcE-hqffjajNZzQwiA7rTHMoR4RLSAxsCE"

    The key is public on purpose: it can only read the demo's sample data. For Claude Desktop, Cursor and other apps, see Connect your AI.

  2. Ask your question. Give your AI this prompt:
    Using mcpdata, are there duplicate order_id values in Demo.Orders for June 2025? Show the ones with the most copies.
What your AI does: the tool call it makes for this prompt, and what came back
query(table_name="Demo.Orders", filters=[{"column": "order_month", "op": "eq", "value": "2025-06"}], group_by=["order_id"], agg=[{"func": "count", "column": "order_id", "alias": "copies"}], sort=[{"column": "copies", "desc": true}], limit=10) → 10 rows (more available)
What you should get · the recipe's real result from its last run
order_idcopies
76519692
78201432
77419202
77844822
76387202
76272562
78079782
78316252
79215262
76586472

Synthetic data: the demo's orders are generated, not real customers.

Ask Claude with this prompt Ask ChatGPT with this prompt

The assistant needs mcpdata connected (step 1) to run the query.

Why this works

Spotting duplicates needs every row of the slice you check, because the two copies can be far apart. A count per key in the database checks them all at once; the AI only sees the handful of keys that repeat. A key-per-row count over a whole 10-million-row table makes 10 million groups, which is more working memory than a small shared instance has; the database stops such a query with a clear message instead of failing, so ask for one month (or one load) at a time, or run it on a dedicated instance.

Related questions