Keep the Latest Status Row per Task with SQL ROW_NUMBER

How does DuckDB keep the latest status row for each task?

DuckDB keeps one latest status row per task by ranking events with ROW_NUMBER() and filtering to position 1 with QUALIFY.

SELECT task_id, status, updated_at, event_id FROM task_events QUALIFY ROW_NUMBER() OVER (PARTITION BY task_id ORDER BY updated_at DESC, event_id DESC) = 1;

The timestamp defines recency; the unique event_id breaks timestamp ties. For equal timestamps, the higher ID wins.

This produces a deterministic selection from the same input, but a higher ID does not necessarily mean a later business event. The source system must support that interpretation, or the reporting rules must accept it as the tie-breaker.

What sample task history demonstrates a timestamp tie?

The sample task_events history contains seven events across three tasks, including two T-101 events with the same newest timestamp.

Each row records a status change rather than replacing an earlier row. All timestamps follow a shared UTC convention.

task_id	status	updated_at	event_id
T-101	blocked	2026-04-14 09:00:00	201
T-101	in_progress	2026-04-14 11:00:00	202
T-101	done	2026-04-14 11:00:00	203
T-102	queued	2026-04-14 09:15:00	204
T-102	in_progress	2026-04-14 10:30:00	205
T-103	in_progress	2026-04-14 08:45:00	206
T-103	blocked	2026-04-14 12:00:00	207

For T-101, events 202 and 203 tie on updated_at, so descending event ID selects event 203, with status done. T-102 and T-103 each have a single newest timestamp; their IDs do not affect selection.

How do you set up the latest-status example in DuckDB?

Create the sample table in a scratch DuckDB database, insert the seven events, and execute the latest-row query in the same connection.

1. Open a DuckDB SQL session.

   Use a DuckDB command-line session, notebook connection, or connected SQL editor. If a production table already has the name task_events, use a separate scratch database; do not replace production data.

2. Create the table and insert the sample events.

   CREATE TABLE task_events (
       task_id VARCHAR NOT NULL,
       status VARCHAR NOT NULL,
       updated_at TIMESTAMP NOT NULL,
       event_id BIGINT PRIMARY KEY
   );
   
   INSERT INTO task_events (task_id, status, updated_at, event_id)
   VALUES
       ('T-101', 'blocked',     TIMESTAMP '2026-04-14 09:00:00', 201),
       ('T-101', 'in_progress', TIMESTAMP '2026-04-14 11:00:00', 202),
       ('T-101', 'done',        TIMESTAMP '2026-04-14 11:00:00', 203),
       ('T-102', 'queued',      TIMESTAMP '2026-04-14 09:15:00', 204),
       ('T-102', 'in_progress', TIMESTAMP '2026-04-14 10:30:00', 205),
       ('T-103', 'in_progress', TIMESTAMP '2026-04-14 08:45:00', 206),
       ('T-103', 'blocked',     TIMESTAMP '2026-04-14 12:00:00', 207);

   TIMESTAMP sorts time values rather than text. It carries no time zone, so this sample relies on the shared UTC convention. The primary key enforces unique, non-null event IDs; the other constraints prevent missing task IDs, statuses, and timestamps.

3. Execute the latest-row query.

   Use the query under the first heading without a status filter. For another dataset, map its task identifier, recency timestamp, and unique tie-breaker to the corresponding columns.

4. Compare the selected records with the expected output.

   Each task should appear once, with event 203 winning for T-101. Repeating the setup encounters an existing table; use a fresh scratch database to recreate the example.

Which status rows should the DuckDB query return?

The DuckDB latest-row query selects event 203 for T-101, event 205 for T-102, and event 207 for T-103.

task_id	status	updated_at	event_id
T-101	done	2026-04-14 11:00:00	203
T-102	in_progress	2026-04-14 10:30:00	205
T-103	blocked	2026-04-14 12:00:00	207

This table is displayed in task ID order, but the query does not guarantee display order. Add ORDER BY task_id after the QUALIFY condition and before the semicolon to sort the report.

Only tasks present in task_events appear. To include tasks without events, start from a separate task table and left join the latest-event result.

Why do ROW_NUMBER() and QUALIFY select one complete latest status row per task?

ROW_NUMBER() and QUALIFY select one complete latest status row per task by numbering each task’s events newest-first and retaining position 1.

FROM task_events supplies the event history, keeping each status attached to its timestamp and event ID. Inside OVER (...), PARTITION BY task_id establishes a separate ranking group for each task while preserving individual records rather than collapsing them into aggregate values.

Next, ORDER BY updated_at DESC, event_id DESC places the newest timestamp first and uses the higher event ID as the secondary preference. For T-101, that ordering puts event 203 ahead of event 202. ROW_NUMBER() then assigns distinct positions starting at 1, matching the requirement for a single selected event rather than shared ranks.

QUALIFY ... = 1 filters the window result directly, avoiding an extra subquery solely to filter row numbers. Finally, SELECT task_id, status, updated_at, event_id returns the four requested fields from each retained record, preserving the selected event’s complete status information.

Why does ROW_NUMBER() preserve the correct status row?

ROW_NUMBER() preserves the selected event’s complete row because it ranks existing rows instead of aggregating their columns independently.

Query component	Effect
PARTITION BY task_id	Creates one ranking group per task
ORDER BY updated_at DESC, event_id DESC	Places the newest event first, resolving timestamp ties
ROW_NUMBER()	Assigns distinct positions starting at 1
QUALIFY ... = 1	Retains the first row in each group

Without partitioning, the query retains only one event across the entire table. Without a unique tie-breaker, row numbers remain distinct, but tied rows have no guaranteed winning order.

MAX(updated_at) returns a timestamp, not its associated status. Joining maximum timestamps back to the history returns both newest T-101 events unless another rule resolves the tie.

How do you fix unstable ties, old-status matches, and text-date sorting?

Fix latest-status errors by providing a unique window tie-breaker, filtering status after ranking, and converting imported date strings to timestamps.

Tied timestamps produce unstable status choices

Ordering only by timestamp leaves T-101’s events 202 and 203 unresolved.

Exact fix: restore this ordering inside OVER (...):

ORDER BY updated_at DESC, event_id DESC

A final report-level sort cannot fix row selection. If an imported table lacks the sample’s primary key, check the tie-breaker:

SELECT event_id, COUNT(*) AS row_count
FROM task_events
GROUP BY event_id
HAVING COUNT(*) > 1 OR event_id IS NULL;

Returned rows require source-data repair or a different unique, stable tie-breaker. Arbitrary IDs assigned after import do not establish event chronology.

A completed task appears in the blocked-task report

Placing WHERE status = 'blocked' before ranking finds each task’s latest blocked event, not its current status. That incorrectly includes T-101 in a current blocked-task report.

Exact fix: select the latest rows before filtering status:

WITH latest AS (
    SELECT task_id, status, updated_at, event_id
    FROM task_events
    QUALIFY ROW_NUMBER() OVER (
        PARTITION BY task_id
        ORDER BY updated_at DESC, event_id DESC
    ) = 1
)
SELECT task_id, status, updated_at, event_id
FROM latest
WHERE status = 'blocked';

This query retains only T-103 from the sample. Apply the same filter placement when excluding completed tasks.

Imported text dates put an older event first

A VARCHAR timestamp can sort alphabetically rather than chronologically when formats or padding vary.

Exact fix: convert directly parseable values before ranking:

ALTER TABLE task_events
ALTER COLUMN updated_at TYPE TIMESTAMP
USING CAST(updated_at AS TIMESTAMP);

For a known US month/day/year format, use this alternative:

ALTER TABLE task_events
ALTER COLUMN updated_at TYPE TIMESTAMP
USING strptime(updated_at, '%m/%d/%Y %H:%M:%S');

These are alternative conversions for imported text, not additional sample setup steps. Repair malformed values if conversion fails; replacing them with nulls leaves their recency undefined.

When should you avoid selecting one latest row per task?

Avoid the one-row-per-task pattern when the report needs history, all newest-timestamp ties, or reconstructed state from partial updates.

Requirement	Better approach
Every status transition	Preserve history and use LAG(status) with chronological ordering
Every event tied at the newest timestamp	Use RANK() ordered only by updated_at DESC, retaining rank 1
Complete state from partial updates	Reconstruct the event sequence or maintain a current-state table
Large, frequently refreshed dashboards	Consider maintaining current state separately from the historical log

Define recency before choosing the ordering column. Use occurrence time when delayed imports should not replace newer business events. Use ingestion time when the report needs the most recently received record, retaining a unique tie-breaker in either case.