THIS QUERY RETURNS DUPLICATES.
SELECT DISTINCT user_id
FROM orders
ORDER BY created_at;
Why?
Because DISTINCT only applies to the columns you select.
You're selecting:
user_id
But ordering by:
created_at
Now the database has a problem:
Which created_at should it use for each unique user_id?
A user can have multiple orders.
Which timestamp wins?
Some databases reject this query completely.
Others return results that look random enough to ruin your afternoon.
What you probably wanted was:
SELECT DISTINCT ON (user_id)
user_id,
created_at
FROM orders
ORDER BY user_id, created_at DESC;
That gives you:
"latest order per user"
SQL gets weird fast once you combine:
- DISTINCT
- ORDER BY
- GROUP BY
And ORMs make it worse because now the broken query is hiding behind:
.distinct().order_by()
...which somehow passed code review.