Data Cleaning Exercises

QUALIFY in DuckDB: The Cleanest Way to Keep the Latest Row

Keep the latest row per key in DuckDB with QUALIFY and ROW_NUMBER: handle ties and NULL timestamps, choose WHERE or QUALIFY for filters, and compare arg_max.

By Tnas · Published · 12 min read

A SQL editor panel showing a QUALIFY ROW_NUMBER() query with NULLS LAST and an update_id tie-breaker, next to the article title.

To keep the latest row per key in DuckDB, number the rows in each key from newest to oldest and keep row 1: QUALIFY ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC NULLS LAST, update_id DESC) = 1. QUALIFY filters on a window function the way HAVING filters on an aggregate, so there is no subquery to write. The one-liner is easy. Getting the same answer every time is harder: ties, NULL timestamps and the placement of other filters each change which row survives. This tutorial walks through all three on a small table of customer status changes.

Key Takeaways

  • QUALIFY is applied after window functions are computed, so it can filter on ROW_NUMBER(), RANK() or any other window result without a subquery.
  • Always end the window's ORDER BY with a unique column. Without it, tied rows make the "latest" row arbitrary.
  • DuckDB sorts NULL last even in DESC order; PostgreSQL sorts it first. Write NULLS LAST explicitly so the intent survives a port.
  • A condition in WHERE runs before the ranking and a condition in QUALIFY runs after it. "Latest non-cancelled status" and "latest status, if it is not cancelled" are different queries.
  • arg_max is a compact alternative for one column, but it has its own tie and NULL rules.

The example table

Each row is a status change for a customer. Customer C-2 has two changes with the same timestamp, and C-3 has a change with no timestamp, the kind of row a late or broken feed produces.

CREATE TABLE status_updates AS SELECT * FROM (VALUES
  (101, 'C-1', 'trial',     TIMESTAMP '2026-05-01 09:00'),
  (102, 'C-1', 'active',    TIMESTAMP '2026-05-15 10:30'),
  (103, 'C-1', 'cancelled', TIMESTAMP '2026-06-02 08:00'),
  (104, 'C-2', 'trial',     TIMESTAMP '2026-05-03 14:00'),
  (105, 'C-2', 'active',    TIMESTAMP '2026-05-20 11:00'),
  (106, 'C-2', 'paused',    TIMESTAMP '2026-05-20 11:00'),
  (107, 'C-3', 'trial',     TIMESTAMP '2026-05-10 16:00'),
  (108, 'C-3', 'active',    NULL)
) t(update_id, customer_id, status, updated_at);

All outputs below come from DuckDB 1.5.1, with the column-type row omitted.

What QUALIFY does

A window function such as ROW_NUMBER() computes a value for every row while keeping all rows. The standard SQL way to filter on that value is a subquery, because WHERE runs before window functions exist:

SELECT customer_id, status, updated_at
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM status_updates
)
WHERE rn = 1;

Putting the window function straight into WHERE fails with Binder Error: WHERE clause cannot contain window functions!. QUALIFY is a clause for exactly this filter. The DuckDB QUALIFY documentation describes it as filtering window results "similar to how a HAVING clause" filters aggregates. The same query becomes:

SELECT customer_id, status, updated_at
FROM status_updates
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1
ORDER BY customer_id;

┌─────────────┬───────────┬─────────────────────┐
│ customer_id │  status   │     updated_at      │
├─────────────┼───────────┼─────────────────────┤
│ C-1         │ cancelled │ 2026-06-02 08:00:00 │
│ C-2         │ active    │ 2026-05-20 11:00:00 │
│ C-3         │ trial     │ 2026-05-10 16:00:00 │
└─────────────┴───────────┴─────────────────────┘

It is shorter, and the filter sits next to the columns it filters. You can also refer to a window column by its alias, for example SELECT ..., ROW_NUMBER() OVER (...) AS rn ... QUALIFY rn = 1, which is handy when you want to see the number while debugging.

The logical order of a DuckDB SELECT is roughly: FROM, WHERE, GROUP BY, HAVING, window functions, QUALIFY, DISTINCT, ORDER BY, LIMIT. Keep that order in mind; most of the surprises below come from it.

Look at the numbering before you filter

The result above looks reasonable, but C-2 deserves a second look. Show the row numbers instead of filtering on them:

SELECT customer_id, status, updated_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
FROM status_updates
ORDER BY customer_id, rn;

┌─────────────┬───────────┬─────────────────────┬────┐
│ customer_id │  status   │     updated_at      │ rn │
├─────────────┼───────────┼─────────────────────┼────┤
│ C-1         │ cancelled │ 2026-06-02 08:00:00 │  1 │
│ C-1         │ active    │ 2026-05-15 10:30:00 │  2 │
│ C-1         │ trial     │ 2026-05-01 09:00:00 │  3 │
│ C-2         │ active    │ 2026-05-20 11:00:00 │  1 │
│ C-2         │ paused    │ 2026-05-20 11:00:00 │  2 │
│ C-2         │ trial     │ 2026-05-03 14:00:00 │  3 │
│ C-3         │ trial     │ 2026-05-10 16:00:00 │  1 │
│ C-3         │ active    │ NULL                │  2 │
└─────────────┴───────────┴─────────────────────┴────┘

Two things stand out. C-2's active and paused rows share a timestamp, so which one gets number 1 is up to the engine. And C-3's row with no timestamp sorted to the bottom even though the order is DESC.

Problem 1: ties make "latest" arbitrary

When two rows tie on every ORDER BY column, ROW_NUMBER() still has to give them different numbers, and nothing in the query says which comes first. The engine is free to pick either, and the pick can change with data size, thread count or version. A cleaning step whose output depends on that is not reproducible.

There are two honest answers.

Break the tie with a rule. If a higher update_id means a later write, add it as the final sort key:

SELECT customer_id, update_id, status, updated_at
FROM status_updates
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY updated_at DESC NULLS LAST, update_id DESC
) = 1
ORDER BY customer_id;

┌─────────────┬───────────┬───────────┬─────────────────────┐
│ customer_id │ update_id │  status   │     updated_at      │
├─────────────┼───────────┼───────────┼─────────────────────┤
│ C-1         │       103 │ cancelled │ 2026-06-02 08:00:00 │
│ C-2         │       106 │ paused    │ 2026-05-20 11:00:00 │
│ C-3         │       107 │ trial     │ 2026-05-10 16:00:00 │
└─────────────┴───────────┴───────────┴─────────────────────┘

C-2 is now paused on every run, because the rule says so.

Keep the tie and flag it. If there is no defensible tie-breaker, RANK() gives tied rows the same number, so = 1 returns all of them:

SELECT customer_id, status, updated_at
FROM status_updates
QUALIFY RANK() OVER (PARTITION BY customer_id ORDER BY updated_at DESC NULLS LAST) = 1
ORDER BY customer_id, status;

┌─────────────┬───────────┬─────────────────────┐
│ customer_id │  status   │     updated_at      │
├─────────────┼───────────┼─────────────────────┤
│ C-1         │ cancelled │ 2026-06-02 08:00:00 │
│ C-2         │ active    │ 2026-05-20 11:00:00 │
│ C-2         │ paused    │ 2026-05-20 11:00:00 │
│ C-3         │ trial     │ 2026-05-10 16:00:00 │
└─────────────┴───────────┴─────────────────────┘

Now C-2 appears twice, which is the truthful answer: the data does not say which status is current. To list only the tied groups for review, filter on a window count:

SELECT customer_id, status, updated_at
FROM status_updates
QUALIFY COUNT(*) OVER (PARTITION BY customer_id, updated_at) > 1
ORDER BY customer_id, status;

┌─────────────┬─────────┬─────────────────────┐
│ customer_id │ status  │     updated_at      │
├─────────────┼─────────┼─────────────────────┤
│ C-2         │ active  │ 2026-05-20 11:00:00 │
│ C-2         │ paused  │ 2026-05-20 11:00:00 │
└─────────────┴─────────┴─────────────────────┘

The lesson on deterministic survivor rules goes deeper into choosing tie-breakers that a reviewer will accept.

Function Tied rows get = 1 returns Use it when
ROW_NUMBER() Different numbers, order arbitrary Exactly one row per key You have a unique tie-breaker in the ORDER BY
RANK() The same number; the next rank is skipped Every row tied for first Ties should stay visible
DENSE_RANK() The same number; no gaps Every row tied for first You also need "second latest" without gaps

Problem 2: NULL timestamps

DuckDB's default is NULLS LAST for both ASC and DESC, so in the numbering above the row with no timestamp ranked after every dated row. PostgreSQL does the opposite for descending sorts: it treats NULL as larger than any value and puts it first under DESC (PostgreSQL sorting documentation). The DuckDB ORDER BY documentation calls out this difference. The same ORDER BY updated_at DESC therefore keeps a different "latest" row for C-3 in the two databases.

Two habits avoid the trap:

  1. Write NULLS LAST (or NULLS FIRST) explicitly in every ranking window, as in the tie-breaker query above. It documents the decision and survives a port.
  2. Decide what a missing timestamp means before ranking. If an undated change cannot be placed in time, it should not quietly lose or win; send it to an exceptions list with WHERE updated_at IS NULL and rank only the dated rows.

The guide on dates and time zones for analysts covers the related problem of timestamps that sort correctly as text but not as instants.

Problem 3: WHERE or QUALIFY changes the answer

Suppose the business asks for each customer's current status, ignoring cancellations. That sentence hides two different questions, and the clause you put the filter in decides which one you answer.

A filter in WHERE removes rows before ranking. This is "the latest status that is not a cancellation":

SELECT customer_id, status, updated_at
FROM status_updates
WHERE status <> 'cancelled'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY updated_at DESC NULLS LAST, update_id DESC) = 1
ORDER BY customer_id;

┌─────────────┬─────────┬─────────────────────┐
│ customer_id │ status  │     updated_at      │
├─────────────┼─────────┼─────────────────────┤
│ C-1         │ active  │ 2026-05-15 10:30:00 │
│ C-2         │ paused  │ 2026-05-20 11:00:00 │
│ C-3         │ trial   │ 2026-05-10 16:00:00 │
└─────────────┴─────────┴─────────────────────┘

A filter in QUALIFY removes rows after ranking. This is "the latest status, but only for customers whose latest status is not a cancellation":

SELECT customer_id, status, updated_at
FROM status_updates
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY updated_at DESC NULLS LAST, update_id DESC) = 1
    AND status <> 'cancelled'
ORDER BY customer_id;

┌─────────────┬─────────┬─────────────────────┐
│ customer_id │ status  │     updated_at      │
├─────────────┼─────────┼─────────────────────┤
│ C-2         │ paused  │ 2026-05-20 11:00:00 │
│ C-3         │ trial   │ 2026-05-10 16:00:00 │
└─────────────┴─────────┴─────────────────────┘

The first query reports C-1 as active, a status they left on 2 June. The second drops C-1, because their current status is a cancellation. For a list of current customers, the second is usually right; for "last known good status", the first is. Decide which question you are answering, then put the filter in the matching clause.

The same reasoning applies to point-in-time questions. To get each customer's status as of the end of May, filter the dates in WHERE so later changes never enter the ranking. Use a half-open boundary (< '2026-06-01') rather than <= '2026-05-31 23:59:59', which would miss a change at 23:59:59.5. The comparison also drops undated rows, since NULL < x is never true, but NULLS LAST stays in the window so the rule reads the same everywhere:

SELECT customer_id, status, updated_at
FROM status_updates
WHERE updated_at < TIMESTAMP '2026-06-01'
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY updated_at DESC NULLS LAST, update_id DESC) = 1
ORDER BY customer_id;

┌─────────────┬─────────┬─────────────────────┐
│ customer_id │ status  │     updated_at      │
├─────────────┼─────────┼─────────────────────┤
│ C-1         │ active  │ 2026-05-15 10:30:00 │
│ C-2         │ paused  │ 2026-05-20 11:00:00 │
│ C-3         │ trial   │ 2026-05-10 16:00:00 │
└─────────────┴─────────┴─────────────────────┘

The exercise Pick the version in force on a date practises this as-of pattern with effective dates.

Naming the window

When the same window appears more than once, define it once with a WINDOW clause. The query reads better and the ranking rule lives in one place:

SELECT customer_id, status, updated_at
FROM status_updates
WINDOW latest_first AS (
  PARTITION BY customer_id
  ORDER BY updated_at DESC NULLS LAST, update_id DESC
)
QUALIFY ROW_NUMBER() OVER latest_first = 1
ORDER BY customer_id;

This returns the same three rows as the tie-breaker query.

The arg_max alternative

If you only need one column from the latest row, arg_max(value, ordering) returns value from the row where ordering is largest, per group (DuckDB aggregate functions):

SELECT customer_id, arg_max(status, updated_at) AS latest_status
FROM status_updates
GROUP BY customer_id
ORDER BY customer_id;

┌─────────────┬───────────────┐
│ customer_id │ latest_status │
├─────────────┼───────────────┤
│ C-1         │ cancelled     │
│ C-2         │ active        │
│ C-3         │ trial         │
└─────────────┴───────────────┘

It is compact, but read the rules before relying on it. The documentation says rows where the value or the ordering expression is NULL are ignored, which is why C-3's undated row did not win. Ties are still arbitrary: C-2 came back as active here, where the tie-broken QUALIFY query said paused. Passing a struct such as (updated_at, update_id) as the ordering adds a tie-breaker, but a struct holding a NULL field is not itself NULL, so it is no longer ignored. On this table that version returns active for C-3: the struct holding the undated row's NULL sorted above every dated one, so the row with no timestamp won.

For a whole row with an explicit tie and NULL policy, QUALIFY ROW_NUMBER() is easier to read and to review. Use arg_max for quick one-column lookups where you have checked the keys for ties.

QUALIFY outside DuckDB

QUALIFY is not part of the SQL standard. DuckDB supports it, and so do several warehouses, including Snowflake and BigQuery. PostgreSQL, MySQL and SQL Server do not, so in those databases use the subquery form from the start of this article. The logic is identical; only the wrapper changes.

PostgreSQL also has DISTINCT ON, which keeps the first row per key according to the ORDER BY, and DuckDB supports it too (DuckDB SELECT documentation):

SELECT DISTINCT ON (customer_id) customer_id, update_id, status, updated_at
FROM status_updates
ORDER BY customer_id, updated_at DESC NULLS LAST, update_id DESC;

It returns the same three rows as the tie-broken QUALIFY query. In PostgreSQL the ORDER BY must start with the DISTINCT ON columns (PostgreSQL SELECT documentation). It is handy for exactly one row per key; QUALIFY is more flexible, because it can keep ties with RANK() or combine the ranking with other conditions.

Practise keeping the latest row

For more window patterns used in cleaning, such as running counts, gaps and previous values, read SQL window functions for cleaning.

Frequently Asked Questions

What is the QUALIFY clause in SQL?

QUALIFY filters rows on the result of a window function, after the window functions are computed. It plays the same role for window functions that HAVING plays for aggregates, and it removes the need for a subquery around ROW_NUMBER().

Can I use a column alias in QUALIFY?

Yes. In DuckDB you can define ROW_NUMBER() OVER (...) AS rn in the SELECT list and write QUALIFY rn = 1. The rn column then appears in the output, which helps while you check the result.

Why does my latest-row query return different rows each time?

Two or more rows tie on every column in the window's ORDER BY, so ROW_NUMBER() breaks the tie arbitrarily. Add a unique column, such as an id or a load sequence number, as the last sort key.

Is QUALIFY faster than a subquery?

Treat them as the same query written two ways: both compute the window and then filter on it. Choose QUALIFY for readability where your database supports it, and check the query plan with EXPLAIN if performance matters.

Sources

Every query in this article was run in DuckDB 1.5.1 against the example table above. Spotted an error? Tell us through the contact page linked in the site footer and it will be corrected.