Why doesn't DISTINCT remove my duplicates? ROW_NUMBER and QUALIFY in DuckDB

Compare DISTINCT with ROW_NUMBER to remove repeated folios in SQL, and keep the latest entry for each sale.

Free course. Your Maestro, an AI, .

In SQL, the first word that comes to mind for removing repeats is DISTINCT. It has the same problem as drop_duplicates() without subset: it compares whole rows. In the March sheet:

SELECT DISTINCT * FROM ventas returns 60 rows. It removes nothing, because the captura (entry) column is different in each one.
Without captura, SELECT DISTINCT on the other columns returns 59. It removes the exact copy of V-0017, but V-0031 is still there twice, because its kilos are different.
There are 58 distinct folios. That's the number of sales that should be left.

DISTINCT answers "which distinct combinations are there?", not "which copy of each sale counts?". For the second question you need to state, as in pandas, the key (the folio) and the rule for choosing (the most recent entry). In SQL you write that with ROW_NUMBER().

SQL

SELECT folio, captura, kilos,
       ROW_NUMBER() OVER (PARTITION BY folio ORDER BY captura DESC) AS n
FROM ventas
WHERE folio IN ('V-0017', 'V-0031', 'V-0040', 'V-0041')
ORDER BY folio, n;

Numbers the copies of each folio, from the most recent entry to the oldest.

Read it piece by piece:

PARTITION BY folio makes one group per folio. That's the key.
ORDER BY captura DESC sorts each group from the most recent entry to the oldest. It's Marisol's rule, written inside the query: it doesn't depend on the order the rows happen to be stored in, which is exactly the problem keep="last" had without sort_values.
ROW_NUMBER() numbers each group: 1, 2, 3...

Run on those four folios, the query gives: V-0017 with entry 18 as 1 and entry 17 as 2; V-0031 with entry 36 (53 kilos) as 1 and entry 32 (5 kilos) as 2; V-0040 and V-0041 with a single row each, both as 1. Keeping the rows where n is 1 means keeping the latest entry of each sale.

60 sales rows
One group per folio
PARTITION BY folio
Most recent entry first
ORDER BY captura DESC
Number each group
ROW_NUMBER()
Keep n = 1
58 sales

Two ways to keep n = 1

You can't write WHERE n = 1 in the same query that calculates n. If you try, DuckDB stops with "Binder Error: WHERE clause cannot contain window functions!". There are two ways around it, and they give the same result.

With QUALIFY, which DuckDB has precisely for filtering on the result of a window function:

SQL

SELECT folio, captura, kilos
FROM ventas
QUALIFY ROW_NUMBER() OVER (PARTITION BY folio ORDER BY captura DESC) = 1;

QUALIFY filters after the window function is calculated, so you don't need a visible number.

With a WITH, the version you'll need if your database doesn't have QUALIFY: first you number, then you filter.

SQL

WITH numeradas AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY folio ORDER BY captura DESC) AS n
  FROM ventas
)
SELECT folio, captura, kilos
FROM numeradas
WHERE n = 1;

The first query numbers; the second filters with WHERE n = 1.

Both return 58 sales in the March sheet, with V-0031 at 53 kilos. And both keep Hotel Mirador's two orders, because they have different folios: ROW_NUMBER only compares rows within the same folio.

Does your database not have QUALIFY? Ask your Maestro how to write the WITH version for your case.

If at your job there's a way to know which copy counts (a modification date, a version number, a status column), it goes in the window's ORDER BY. If there isn't, the decision can't be written in a query and you ask, as you did with Juana in the member list.

Try it

The query below uses DISTINCT and returns 60 rows. Change it so it returns a single sale per folio, the one with the most recent entry. If you get an error with WHERE, ask your Maestro why.

Your turn

Rewrite the query with ROW_NUMBER() to keep the latest entry for each folio (with QUALIFY or with a WITH). Return folio, captura and kilos, ordered by folio. You're done when 58 rows come out and V-0031 has 53 kilos.

Tables: ventas (captura, folio, fecha, cliente, ciudad, producto, kilos, monto, descuento)

ventas · 60 rows

Column	Type
captura Orden en que se capturó el renglón en la hoja (1 es el primero).	BIGINT
folio Folio de la venta.	VARCHAR
fecha Fecha de la venta, AAAA-MM-DD.	VARCHAR
cliente	VARCHAR
ciudad Ciudad del cliente, como se capturó.	VARCHAR
producto	VARCHAR
kilos Kilos vendidos, como se capturaron.	DOUBLE
monto Monto de la venta en pesos, como texto tal como se capturó.	VARCHAR
descuento Descuento en porcentaje; vacío cuando no hubo descuento.	DOUBLE

First rows

captura	folio	fecha	cliente	ciudad	producto	kilos	monto	descuento
1	V-0001	2026-03-01	Abarrotes Doña Mary	Guadalajara	Café tostado	28	$7,784.00	10
2	V-0002	2026-03-01	Grano Fino	Ciudad de México	Café verde	46	4,692	10
3	V-0003	2026-03-02	Hotel Mirador	puebla	Café tostado	45	14,130	NULL

Your query (SQL)

Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your query.

Run it yourself

If your result has 58 rows, also check V-0031: if it says 5 kilos, your ORDER BY is backwards and you kept the oldest entry. It's the same check you did in pandas: counting isn't enough, you have to look at the cases you know.

Check yourself: In the March sheet, `SELECT DISTINCT * FROM ventas` returns 60 rows. What does that tell you?

DuckDB: the QUALIFY clause