Deterministic Tie-Breakers in Latest-Row-Per-Group Queries

• Tied timestamps – two rows in the same group can share the exact same created_at, making both equally eligible for row number one.
• Non-deterministic output – without a secondary column, the engine picks one arbitrarily based on physical order, parallelism, or plan changes.
• Flapping results – the same query can return a different "latest" row on different runs, after a vacuum, or after a plan change.
• Fix – add a unique, stable tie-breaker to the ORDER BY, almost always the primary key: ORDER BY created_at DESC, id DESC.
• RANK alternative – if you genuinely want all rows tied for latest, use RANK() instead of ROW_NUMBER() and accept multiple rows.

What is the latest-row-per-group pattern?

The standard approach uses ROW_NUMBER() partitioned by group and ordered by the timestamp, keeping only the rows where the number equals one.

-- Latest transaction per customer at National Bank of India
SELECT customer_id, txn_id, amount, created_at
FROM (
    SELECT customer_id, txn_id, amount, created_at,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY created_at DESC
           ) AS rn
    FROM transactions
) ranked
WHERE rn = 1;

This works perfectly when created_at is unique per customer. But timestamps are frequently not unique.

When are timestamps not unique?

Timestamps can tie under several common scenarios.

-- Batch import: all rows get the same timestamp
INSERT INTO transactions (customer_id, amount, created_at)
VALUES
    (101, 5000.00, '2026-06-15 10:00:00'),
    (101, 3000.00, '2026-06-15 10:00:00');  -- same timestamp!

-- High-throughput: sub-millisecond precision may not differentiate
-- Coarse column: TIMESTAMP(0) truncates to seconds

What goes wrong without a tie-breaker?

When two rows tie on created_at, ROW_NUMBER() must pick one, but the SQL standard does not define which. The engine may choose based on physical row order, index scan direction, or parallelism.

-- Run 1: returns txn_id = 501 (amount 5000)
-- Run 2: returns txn_id = 502 (amount 3000)
-- Run 3: returns txn_id = 501 again
-- This is non-deterministic / flapping output

A report that should be stable silently changes. Reconciliation breaks. Downstream systems disagree.

How do you fix it with a tie-breaker?

Add a secondary ordering column that is unique and stable: almost always the primary key.

-- Deterministic: tie-breaker on primary key
SELECT customer_id, txn_id, amount, created_at
FROM (
    SELECT customer_id, txn_id, amount, created_at,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY created_at DESC, txn_id DESC  -- tie-breaker!
           ) AS rn
    FROM transactions
) ranked
WHERE rn = 1;

Now when timestamps tie, the higher txn_id wins. The result is the same every time.

What properties must the tie-breaker have?

The tie-breaker must be unique within the partition and stable over time.

| Property | Why it matters |
|---|---|
| Unique within partition | No residual ambiguity after the tie-break |
| Stable (immutable) | Results do not change if the tie-breaker value is updated |
| Monotonic | Consistent "latest" semantics (higher id = later insertion) |

A monotonic surrogate key like a SERIAL or BIGSERIAL primary key is ideal.

Does LIMIT 1 have the same flapping problem?

Yes. Even a simple LIMIT 1 over an ORDER BY timestamp without a tie-breaker has the same non-deterministic behavior.

-- Also flaps without a tie-breaker
SELECT * FROM transactions
WHERE customer_id = 101
ORDER BY created_at DESC
LIMIT 1;

-- Fix: add tie-breaker
SELECT * FROM transactions
WHERE customer_id = 101
ORDER BY created_at DESC, txn_id DESC
LIMIT 1;

When should you use RANK instead of ROW_NUMBER?

If you genuinely want all rows tied for latest, use RANK() and accept multiple rows per group.

-- Returns ALL transactions tied for the latest timestamp per customer
SELECT customer_id, txn_id, amount, created_at
FROM (
    SELECT customer_id, txn_id, amount, created_at,
           RANK() OVER (
               PARTITION BY customer_id
               ORDER BY created_at DESC
           ) AS rnk
    FROM transactions
) ranked
WHERE rnk = 1;

Use RANK when multiple results are acceptable ("all latest"). Use ROW_NUMBER with a tie-breaker when you need exactly one.

How do you implement this in Java?

Rajesh writes the deterministic latest-transaction query in the repository layer.

package com.bank.repository;

import java.sql.*;
import java.math.BigDecimal;

public class TransactionRepository {

    public record LatestTransaction(
        long txnId, long customerId, BigDecimal amount, Timestamp createdAt
    ) {}

    public LatestTransaction findLatestByCustomer(Connection conn, long customerId)
            throws SQLException {
        String sql = "SELECT txn_id, customer_id, amount, created_at "
                   + "FROM transactions "
                   + "WHERE customer_id = ? "
                   + "ORDER BY created_at DESC, txn_id DESC "
                   + "LIMIT 1";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, customerId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return new LatestTransaction(
                        rs.getLong("txn_id"),
                        rs.getLong("customer_id"),
                        rs.getBigDecimal("amount"),
                        rs.getTimestamp("created_at")
                    );
                }
                return null;
            }
        }
    }
}

What index supports this query efficiently?

A composite index matching the partition and order columns avoids a sort.

-- Optimal index for latest-per-customer with tie-breaker
CREATE INDEX idx_txn_customer_latest
    ON transactions (customer_id, created_at DESC, txn_id DESC);

The engine seeks to customer_id = 101, walks backward through the descending index, and returns the first row without scanning the whole table.

What are common interview follow-up questions?

• What if the tie-breaker itself is not unique? (Then the result is still non-deterministic; you need a column that is guaranteed unique within the partition.)
• Can updated_at be a reliable tie-breaker? (No, because it changes over time and is not stable.)
• How do you handle this in a CTE? (Same pattern: add the tie-breaker to the ORDER BY inside the window function.)