Introduction

A plain LEFT JOIN returns all matching rows from the right table, which duplicates parent rows whenever multiple children exist. If you need only one child per parent, you must define what first means and encode that rule in SQL. The most reliable methods use deterministic ordering with window functions or database-specific alternatives.

Define First Row Explicitly

The phrase first row is ambiguous unless tied to a sort rule. Common definitions include earliest timestamp, highest priority, or smallest identifier.

A correct query should always answer these two questions:

• which column defines ordering
• how ties are broken consistently

Without this, output may vary by execution plan.

Portable Solution with ROW_NUMBER

Window functions are the most portable approach across modern SQL engines.

1WITH ranked_child AS (
2  SELECT
3    c.parent_id,
4    c.child_id,
5    c.status,
6    c.created_at,
7    ROW_NUMBER() OVER (
8      PARTITION BY c.parent_id
9      ORDER BY c.created_at ASC, c.child_id ASC
10    ) AS rn
11  FROM child c
12)
13SELECT
14  p.parent_id,
15  p.name,
16  rc.child_id,
17  rc.status,
18  rc.created_at
19FROM parent p
20LEFT JOIN ranked_child rc
21  ON rc.parent_id = p.parent_id
22 AND rc.rn = 1;

This preserves all parent rows and selects one deterministic child row when available.

SQL Server Pattern with OUTER APPLY

In SQL Server, OUTER APPLY plus TOP 1 can be concise and readable.

1SELECT
2  p.parent_id,
3  p.name,
4  c1.child_id,
5  c1.status,
6  c1.created_at
7FROM parent p
8OUTER APPLY (
9  SELECT TOP 1 c.*
10  FROM child c
11  WHERE c.parent_id = p.parent_id
12  ORDER BY c.created_at ASC, c.child_id ASC
13) c1;

This style is convenient when first-row logic includes additional filtering or computed ranking.

PostgreSQL Pattern with DISTINCT ON

PostgreSQL has a compact option using DISTINCT ON.

1SELECT DISTINCT ON (p.parent_id)
2  p.parent_id,
3  p.name,
4  c.child_id,
5  c.created_at
6FROM parent p
7LEFT JOIN child c
8  ON c.parent_id = p.parent_id
9ORDER BY p.parent_id, c.created_at ASC, c.child_id ASC;

This is elegant but less portable than the window-function approach.

Preserve LEFT JOIN Behavior

A common mistake is adding child filters in the outer WHERE clause, which can drop unmatched parents and effectively turn the query into inner-join behavior.

Prefer filtering in one of these places:

• inside the ranking CTE
• inside OUTER APPLY subquery
• in the ON clause

That keeps unmatched parent rows in the result set.

Indexing for Performance

Selecting one child per parent can be expensive on large child tables unless indexes match partition and order rules.

Useful index pattern:

• '(parent_id, created_at, child_id)'

This helps the database find top-ranked child rows quickly. Validate with execution plans, not assumptions.

Deterministic Tie Handling

If several child rows share the same timestamp, include a stable tie-breaker such as child_id. Deterministic output is essential for reports, caching layers, and audit logic.

For critical data pipelines, add tests that lock expected row identity for representative parent groups.

Example Validation Query

You can verify one-row-per-parent behavior by checking duplicates in the result.

1WITH result AS (
2  SELECT
3    p.parent_id,
4    rc.child_id
5  FROM parent p
6  LEFT JOIN (
7    SELECT
8      c.parent_id,
9      c.child_id,
10      ROW_NUMBER() OVER (
11        PARTITION BY c.parent_id
12        ORDER BY c.created_at ASC, c.child_id ASC
13      ) AS rn
14    FROM child c
15  ) rc
16    ON rc.parent_id = p.parent_id
17   AND rc.rn = 1
18)
19SELECT parent_id, COUNT(*) AS rows_per_parent
20FROM result
21GROUP BY parent_id
22HAVING COUNT(*) > 1;

This query should return no rows.

Common Pitfalls

• Using DISTINCT to hide duplicates without defining selection order.
• Using TOP 1 or LIMIT 1 without ORDER BY.
• Applying child filters in WHERE and dropping unmatched parents.
• Forgetting tie-break columns and getting non-deterministic results.
• Ignoring index design for parent-grouped selection queries.

Summary

• 'LEFT JOIN alone does not select one child row per parent.'
• Define first-row logic with explicit deterministic ordering.
• Use ROW_NUMBER for portability, or engine-specific shortcuts where appropriate.
• Keep filters in ranking or join logic to preserve outer-join semantics.
• Add proper indexes and tie-breakers for stable, performant queries.