Keep the Latest Status Row per Task with SQL ROW_NUMBER
How do I use SQL ROW_NUMBER() in DuckDB to keep the latest status row for each task and resolve timestamp ties deterministically?
In DuckDB, partition events by task_id and order by updated_at DESC, then event_id DESC. QUALIFY keeps row 1. The higher event ID settles timestamp ties, but only represents later chronology if your source or reporting rules support that interpretation.
To get one newest status row for each task, rank its events from newest to oldest and use DuckDB’s QUALIFY clause to keep position 1. Include a unique tie-breaker in the sort so matching timestamps still yield a single row. Choose a tie-breaker only if its meaning fits the report.
How does DuckDB keep the latest status row for each task?
Rank every task’s events with ROW_NUMBER(), sort by timestamp and a tie-breaker, then use QUALIFY to keep row 1. DuckDB describes ROW_NUMBER() as a window function. Since QUALIFY filters the window result, there is no need to wrap the query in another select.
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 determines recency, while the unique event_id resolves a tie. If two timestamps match, the higher ID wins. Unique IDs make that choice deterministic, but they do not establish which event happened later in business terms. Use the ID as a chronology rule only when the source supports that interpretation or the reporting rules allow it.
Each result keeps the status with its timestamp and ID. That matters when a report needs the status from the event that set it. Pairing a maximum timestamp with a status selected separately could combine values from different events. For another SQL reporting method, see how to calculate a dashboard completion rate without averaging percentages.
What sample task history demonstrates a timestamp tie?
The sample task_events table has seven events across three tasks. Two T-101 events share the newest timestamp, making this task a useful check on how the tie-breaker affects the selected row.
Each row represents a status change, so earlier events remain in the table. Every timestamp uses the same 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 have the same updated_at. Descending event ID order selects event 203, which has the done status. T-102 and T-103 each have only one newest timestamp, so their IDs do not affect the result. If you use a different event source or change the sample, confirm that the tie-breaker is still unique and that the timestamp represents the kind of recency the report needs.
How do you set up the latest-status example in DuckDB?
Set up the example by creating the table in a scratch DuckDB database, inserting all seven events, and running the latest-row query in that same connection.
-
Open a DuckDB SQL session.
Use the DuckDB command line, a notebook, or a SQL editor to connect. If a production table already has the name
task_events, work in a separate scratch database rather than replacing production data. -
Create the table and insert the sample events.
CREATE TABLE task_events ( task_id VARCHAR(100) NOT NULL, status VARCHAR(100) 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);TIMESTAMPlets DuckDB sort these values as times, rather than as text. It has no time zone, so this sample assumes all values use the same UTC convention. The primary key makes every event ID unique and non-null. The other constraints require a task ID, status, and timestamp for each row. -
Execute the latest-row query.
Run the query under the first heading as written, without adding a status filter. For another dataset, substitute its task identifier, recency timestamp, and unique tie-breaker columns. If dates are stored as text, convert them to timestamps before ranking; text order can put dates in the wrong sequence.
-
Compare the selected records with the expected output.
Expect one row per task, with event 203 selected for T-101. Running the setup again will encounter the table that already exists, so use a fresh scratch database to recreate the example.
The same query in MySQL and PostgreSQL
QUALIFY is not standard SQL. MySQL 8.4 and PostgreSQL reject it, so rank the rows in a subquery and filter the row number outside it. This version also runs in DuckDB and returns the same three rows:
SELECT task_id, status, updated_at, event_id
FROM (
SELECT task_id, status, updated_at, event_id,
ROW_NUMBER() OVER (
PARTITION BY task_id
ORDER BY updated_at DESC, event_id DESC
) AS rn
FROM task_events
) ranked
WHERE rn = 1;
MySQL added window functions in version 8.0, so this does not work in MySQL 5.7 or earlier. PostgreSQL documents ROW_NUMBER() with its other window functions. The latest row SQL builder writes this query for your own table and column names.
Which status rows should the DuckDB query return?
The query returns event 203 for T-101, event 205 for T-102, and event 207 for T-103. The table below sorts them by task ID for easier reading; the query itself does not guarantee that display order.
| 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 |
To sort the report, put ORDER BY task_id after the QUALIFY condition and before the semicolon. The order inside ROW_NUMBER() picks the winning event for each task. It does not sort the final result.
Only tasks found in task_events appear in this query’s output. To include tasks with no events, start from a separate task table and left join the latest-event result. Those unmatched tasks have null event fields, so the report needs a clear rule for displaying or handling them. For a related example, see how to find projects with no tasks using a SQL LEFT JOIN.
Why do ROW_NUMBER() and QUALIFY select one complete latest status row per task?
ROW_NUMBER() assigns each event a position within its task, and QUALIFY ... = 1 keeps the first event in each task group. DuckDB documents both window functions and the QUALIFY clause (window functions, QUALIFY).
FROM task_events provides the event history, with each status kept beside its timestamp and event ID. Within OVER (...), PARTITION BY task_id creates a separate ranking group for every task. This keeps complete event rows instead of reducing the data to aggregate values.
Then ORDER BY updated_at DESC, event_id DESC puts the newest timestamp first and uses the higher event ID to break ties. For T-101, event 203 ranks ahead of event 202. ROW_NUMBER() gives every row a distinct position, starting at 1, which fits a report that needs one event per task. To retain every event tied at the newest timestamp, use RANK() ordered only by updated_at DESC and keep rank 1. A task may then have multiple rows.
QUALIFY ... = 1 filters the window result without a subquery whose only job is to filter row numbers. The select list returns the four requested fields from each remaining record, leaving the chosen status attached to its event. For more reporting-data examples, visit the spreadsheets and data topic page.
| 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 returns one event from the whole table. Without a unique tie-breaker, row numbers are still distinct, but the database does not guarantee which tied row comes first.
MAX(updated_at) gives you a timestamp, not its status. Joining that maximum back to the event history would return both newest T-101 events unless another rule breaks the tie. I prefer ranking complete event rows when the report needs one consistent status record.
How do you fix unstable ties, old-status matches, and text-date sorting?
To fix latest-status errors, add a unique tie-breaker to the window ordering, apply status filters after ranking, and convert imported date strings to timestamps. These are distinct problems. Before changing source data, check where the filter runs and what rule orders tied events.
Tied timestamps produce unstable status choices
Sorting only by timestamp leaves the query without a rule for choosing between T-101 events 202 and 203.
Exact fix: put this ordering inside OVER (...):
ORDER BY updated_at DESC, event_id DESC
Sorting the final report does not decide which event wins. If an imported table lacks the sample’s primary key, check whether the tie-breaker is unique:
SELECT event_id, COUNT(*) AS row_count
FROM task_events
GROUP BY event_id
HAVING COUNT(*) > 1 OR event_id IS NULL;
Rows returned by this check need source-data repair or another unique, stable tie-breaker. IDs assigned after import without a meaningful order do not establish event chronology.
A completed task appears in the blocked-task report
Putting WHERE status = 'blocked' before ranking makes the query select each task’s newest blocked event, not its current status. That would put T-101 in a report of tasks that are currently blocked.
Exact fix: rank all events first, then filter the selected rows by 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';
Only T-103 remains in the sample. Use the same filter placement when excluding completed tasks. Filtering before ranking changes which events the query considers, and that can change its result.
Imported text dates put an older event first
A VARCHAR timestamp may sort alphabetically rather than by time if formats or padding differ. Convert the values before ranking so DuckDB compares timestamps instead of inconsistent text.
Exact fix: convert values that can be parsed as timestamps:
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 ways to convert imported text, not additional steps for the sample setup. If conversion fails, fix malformed values. Replacing them with nulls leaves their recency undefined.
When should you avoid selecting one latest row per task?
Do not use the one-row-per-task pattern when the report needs the full history, every tie at the newest timestamp, or state rebuilt from partial updates. Pick a ranking rule that matches what the report needs to show.
| 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 |
Decide what recency means before choosing an ordering column. Use occurrence time when delayed imports should not displace newer business events. Use ingestion time when the report should show the record received most recently. In either case, include a unique tie-breaker.
If tasks may have no events, decide whether to include them before writing the latest-event query. If several events can share a task’s newest timestamp, decide whether the report needs one selected row or all tied rows. Those choices determine whether to use ROW_NUMBER() or RANK(), and whether the query should start from event history or a complete task list.