How do I find the min and max per group?
Recipe verified on
Ran on the public demo (read-only)
Short answer
Ask for min and max of the column with a group-by. Your AI gets the lowest and highest value per group in one query, here the smallest and largest order per product category, with the order count.
Steps
- 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.
- Ask your question. Give your AI this prompt:
Using mcpdata, for each category in Demo.Orders, what are the smallest and largest order revenue?
What your AI does: the tool call it makes for this prompt, and what came back
query(table_name="Demo.Orders", group_by=["category"], agg=[{"func": "min", "column": "revenue", "alias": "smallest"}, {"func": "max", "column": "revenue", "alias": "largest"}, {"func": "count", "column": "order_id", "alias": "orders"}], sort=[{"column": "category", "desc": false}]) → 12 rows
| category | smallest | largest | orders |
|---|---|---|---|
| Accessories | 8.56 | 564.72 | 740,027 |
| Audio | 26.56 | 2,090.16 | 881,049 |
| Cameras | 209.68 | 9,361.31 | 835,065 |
| Gaming | 27.04 | 2,878.48 | 760,056 |
| Laptops | 571.81 | 11,736.00 | 860,772 |
| Monitors | 116.49 | 5,179.84 | 915,522 |
| Networking | 27.11 | 1,758.42 | 825,900 |
| Phones | 237.94 | 7,184.52 | 855,129 |
| Smart Home | 18.08 | 1,887.12 | 829,541 |
| Software | 12.55 | 1,415.12 | 745,506 |
| Storage | 35.55 | 3,409.00 | 907,269 |
| Tablets | 155.07 | 5,623.45 | 849,164 |
Synthetic data: the demo's orders are generated, not real customers.
The assistant needs mcpdata connected (step 1) to run the query.
Why this works
Extremes are exactly what sampling misses: the largest order is, by definition, rare. The database scans every row for each group, so the min and max are the real ones.