Selecting the latest record sounds simple until two records share the same timestamp. A query may return one today and another after a change in its execution plan, even though the underlying business data appears unchanged.
ROW_NUMBER() is useful for choosing one row from each group, but it does not invent a business rule for ties. A reliable query needs an ordering that explains which row should win.
Give every candidate a clear position
Oracle documents that ROW_NUMBER() assigns a distinct sequence number within the relevant partition, beginning with one. Its guidance emphasizes deterministic ordering for consistent results.
Consider a hypothetical event table with a non-null event time and a unique event identifier:
SELECT customer_id, event_id, event_time
FROM (
SELECT customer_id,
event_id,
event_time,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY event_time DESC, event_id DESC
) AS rn
FROM customer_events
)
WHERE rn = 1
ORDER BY customer_id;The timestamp establishes recency. The unique identifier supplies an explicit tie-breaker.
This example defines the highest identifier as the winner when timestamps match. That is a policy choice, not proof that the identifier represents a later real-world event.
Understand what happens without a tie-breaker
Oracle’s analytic-function documentation explains that ROW_NUMBER() still assigns different numbers to tied rows. If the ordering does not establish a total order, the assignment can be nondeterministic.
Suppose two events for the same customer both have a timestamp of 10:15:00. Ordering only by that timestamp leaves their relative position unspecified.
The query may look stable during testing because a small dataset repeatedly produces the same result. That observed consistency does not add a missing ordering rule.
A better review asks whether two eligible rows can still tie after every listed sort expression has been considered.
Decide whether one winner is actually required
Before adding a tie-breaker, confirm the report’s purpose.
A support dashboard might need one representative event per customer. An audit report might need to preserve every event at the latest recorded time. Those requirements should not use the same selection rule merely because both include the word “latest.”
If the report must show all tied records, choosing exactly one row changes its meaning. Resolve that product question before polishing the SQL.
Also decide how incomplete timestamps should be handled. The example assumes they are present; a real application must define whether missing values are excluded, flagged, or ordered explicitly.
Test with deliberately ambiguous data
Create a small review dataset containing one customer with a single event, another with several different times, and another with two identical times.
Write down the expected winner for each group. Then add a fourth case that reflects a realistic edge condition in your application.
Keep selection order and presentation order distinct in your review. The analytic ordering chooses the row within each customer group. The final ordering determines how the selected results are displayed.
A query becomes easier to maintain when the tie-breaker has an explanation. Future readers should understand both how the result is selected and why that selection matches the report’s purpose.