Back to Blog

Why Your AI's 'Latest Order Per Customer' Picks the Wrong Row

Asked for each customer's most recent order and got the latest date — attached to the wrong amount or status. GROUP BY + MAX(date) then joining back on date alone matches every order that shares that timestamp, or a different order entirely.

You ask for "each customer's most recent order — date, status, amount." The dates look right. Half the statuses look like older orders. Nothing errored. The AI grouped by customer, took MAX(created_at), and then picked the other columns from a random row in that group — or joined back on date and matched two orders at once.

MAX(date) is not "the latest row"

SELECT customer_id, MAX(created_at), amount, status
FROM orders
GROUP BY customer_id

Postgres rejects this (amount must appear in GROUP BY or an aggregate). That's the honest failure.

MySQL, if ONLY_FULL_GROUP_BY is off, accepts it and fills amount / status from an arbitrary row in the group. The date is the real max. The money is leftover from whichever row the planner touched. The result looks like a latest-order report and isn't one.

The "fix" the model tries next is usually:

SELECT o.*
FROM orders o
JOIN (
  SELECT customer_id, MAX(created_at) AS max_at
  FROM orders
  GROUP BY customer_id
) t ON t.customer_id = o.customer_id AND t.max_at = o.created_at

That is correct only if no customer has two orders with the same created_at. Bulk imports and "same second" checkouts violate that constantly. Then you get two "latest" rows per customer — fan-out — or you LIMIT/DISTINCT and silently drop one.

Same family as LIMIT without a unique ORDER BY: ties are not "close enough."

The versions that actually mean "one latest row"

Postgres DISTINCT ON:

SELECT DISTINCT ON (customer_id)
  customer_id, created_at, amount, status, id
FROM orders
ORDER BY customer_id, created_at DESC, id DESC

Portable window function:

SELECT customer_id, created_at, amount, status, id
FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders o
) x
WHERE rn = 1

The id DESC tiebreaker is the same habit as stable pagination: timestamps collide; primary keys don't.

How to catch it

Ask for a customer you know has two orders close together (or a bulk import). Check that status and amount belong to the same row as the date — not just that the date is the max. If you only check the date, this bug passes.

Pick one customer_id.
Show all of their orders (id, created_at, amount, status).
Then show the 'latest' row your query returned.
Do id / amount / status all come from the newest order?

The prompt that heads this off

For "latest/most recent row per group," don't SELECT non-aggregated
columns next to MAX(date). Use DISTINCT ON (Postgres) or ROW_NUMBER()
PARTITION BY ... ORDER BY date DESC, id DESC, then keep rn = 1.
If you join back on MAX(date), also join on a unique id, or say
when ties can duplicate rows.

The short version

The latest timestamp in a group is easy. The latest row is that timestamp plus every other column from the same row, with a unique tiebreaker. MAX(created_at) only does the first part — and MySQL will happily pretend it did the rest.

Read-only MCP gateway: mcpserver.design.

Related reading:

← Back to Blog