The Pitfall of ORDER BY Timestamp (And How to Fix It)

As developers, we often need to fetch the "latest" record from a database. It could be the most recent status update, the last comment on a post, or the newest entry in a history table. The SQL seems obvious, right? You just ORDER BY a timestamp column and grab the top one.

-- Find the latest status for a model
SELECT model_status
  FROM model_status_history
 WHERE model_uid = 'some-uid'
 ORDER BY created_at DESC
 LIMIT 1;

This query looks perfectly logical. But lurking beneath its simplicity is a subtle bug that can cause inconsistent results, especially in high-traffic applications. The "latest" record you get back might not actually be the one you expect.

The Problem: The Tie

The issue arises when your system records multiple events within the same time resolution. For example, if your created_at column stores time down to the millisecond, what happens when two records are inserted in the exact same millisecond?

When the database sees two rows with identical created_at values, it considers them tied. Without a clear "tie-breaker" rule, the database makes no guarantee about which row will come first. In one query, it might return Row A. A moment later, under slightly different conditions, it might return Row B. This is called a non-deterministic sort, and it's a recipe for unpredictable behaviour.

The Solution: Creating a Deterministic Sort

To fix this, we need to give the database a tie-breaker—a second rule to apply when the first one isn't enough. The perfect candidate is a column that is guaranteed to be unique and sequential: the primary key id.

Method 1: The Tie-Breaker

Since a primary key is unique, it can break any tie. We can add it as a second condition to our ORDER BY clause.

-- First, sort by time. If there's a tie, sort by ID.
ORDER BY created_at DESC, id DESC

This tells the database:

1. First, sort all records by created_at in descending order.
2. If any group of records has the exact same created_at value, sort that specific group by id in descending order.

Because id is unique, a tie is now impossible. The row with the highest id (the one inserted last) will always be chosen. Your query is now deterministic—it will return the same, correct result every single time.

Method 2: The Simpler, Faster Way

There's an even more direct approach. If your id is an auto-incrementing integer, it already serves as a perfect timeline of events. A higher id always means the record was created later.

This means you don't even need the timestamp for ordering. You can rely solely on the id.

-- The most reliable way to get the last inserted row
ORDER BY id DESC

This approach is not only simpler but often more performant. Sorting on an indexed, integer primary key is typically faster for a database than sorting on a more complex timestamp or datetime column.

Takeaway

Relying on timestamps alone to find the "latest" record is a risk. Due to the possibility of ties, you can get inconsistent results.

The best practice is simple: when you need the most recent entry, always sort by a unique, sequential column like your primary key. Whether you use it as a tie-breaker or as the sole ordering key, it guarantees your queries are robust, reliable, and predictable.