Introduction

A frequent SQL requirement is to get one row per unique value in one column while still returning related columns from the same row. DISTINCT alone usually cannot express which related row to keep. The correct solution depends on your rule for choosing the representative row, such as latest timestamp, max score, or min id.

Why Plain DISTINCT Is Not Enough

DISTINCT applies to the whole selected row, not just one column.

SELECT DISTINCT customer_id, order_id, created_at
FROM orders;

If a customer has many orders, this still returns many rows because (customer_id, order_id, created_at) tuples differ.

When you want one row per customer_id, you must define which order row wins.

MySQL 8 Solution with Window Functions

In MySQL 8, ROW_NUMBER is often the cleanest pattern.

1WITH ranked AS (
2  SELECT
3    o.*,
4    ROW_NUMBER() OVER (
5      PARTITION BY o.customer_id
6      ORDER BY o.created_at DESC, o.id DESC
7    ) AS rn
8  FROM orders o
9)
10SELECT customer_id, id AS order_id, created_at, total_amount
11FROM ranked
12WHERE rn = 1;

This returns the latest order per customer. You can change the ORDER BY rule to match your business logic.

MySQL 5.7 Compatible Pattern with Join to Aggregate

Without window functions, use a subquery to choose the key per group and then join back.

Example using latest timestamp per customer:

1SELECT o.customer_id, o.id AS order_id, o.created_at, o.total_amount
2FROM orders o
3JOIN (
4  SELECT customer_id, MAX(created_at) AS max_created_at
5  FROM orders
6  GROUP BY customer_id
7) m
8  ON o.customer_id = m.customer_id
9 AND o.created_at = m.max_created_at;

If ties are possible on created_at, add another tie-breaker, such as max id.

1SELECT o.customer_id, o.id AS order_id, o.created_at
2FROM orders o
3JOIN (
4  SELECT customer_id, MAX(id) AS max_id
5  FROM orders
6  GROUP BY customer_id
7) x
8  ON o.customer_id = x.customer_id
9 AND o.id = x.max_id;

Tie-break rules should be explicit so results are deterministic.

Using ANY_VALUE and Why to Be Careful

MySQL supports ANY_VALUE for non-aggregated columns in grouped queries.

SELECT customer_id, ANY_VALUE(order_id) AS order_id
FROM orders
GROUP BY customer_id;

This is valid but not deterministic for arbitrary columns. Use it only when any representative value is acceptable. For most business cases, deterministic selection is required, so prefer window functions or join-back patterns.

Example Dataset and Query Outcome

Suppose this table:

1CREATE TABLE orders (
2  id INT PRIMARY KEY,
3  customer_id INT NOT NULL,
4  created_at DATETIME NOT NULL,
5  total_amount DECIMAL(10,2) NOT NULL
6);

Insert sample rows:

1INSERT INTO orders (id, customer_id, created_at, total_amount) VALUES
2(1, 10, '2026-03-01 10:00:00', 100.00),
3(2, 10, '2026-03-03 08:00:00', 220.00),
4(3, 20, '2026-03-02 09:00:00', 150.00);

With latest-per-customer logic, results should return order 2 for customer 10 and order 3 for customer 20.

Performance and Indexing

These queries can become expensive on large tables. Add indexes that align with grouping and ordering:

CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at);
CREATE INDEX idx_orders_customer_id ON orders(customer_id, id);

For window function approach, proper partition and order indexes reduce sorting overhead.

Always validate query plan with EXPLAIN.

Common Pitfalls

A common mistake is using DISTINCT customer_id, other_columns and assuming one row per customer. Distinct applies to the full selected tuple, so duplicates by customer remain.

Another issue is failing to define tie-break logic. If multiple rows share max timestamp, results can be nondeterministic or duplicated.

Developers also choose patterns unsupported by deployed MySQL version. Window functions require MySQL 8 or newer, so confirm server version before adopting that syntax.

Summary

• DISTINCT alone does not pick a single related row per group.
• Define explicit selection logic such as latest timestamp or highest id.
• Use ROW_NUMBER in MySQL 8 for clear and deterministic queries.
• Use aggregate plus join-back pattern for MySQL 5.7 compatibility.
• Add supporting indexes and verify execution plans for performance.