Keeping one row per key in ride-hail-trips

Keeping one row per key in ride-hail-trips comes up often enough to be worth a note. ride-hail-trips is a convenient thing to try it on — 8,000 rows, one row per completed trip.

select *
from (
  select *,
         row_number() over (partition by trip_id order by pickup_at desc) as rn
  from ride_hail_trips
) ranked
where rn = 1;

The ordering column is the whole decision. row_number() always returns exactly one row per partition, even when two rows tie — which is why it beats distinct on and group by + max for this: you get determinism, and you get every column of the winning row instead of a re-join to fetch them.

Run it against the real file in the SQL playground — the engine there is enough of a SQL implementation to execute this as written, and the dataset is already loaded. The pattern page has the version with the failure modes spelled out.

Window functions are the difference between SQL that describes rows and SQL that describes sequences. Almost every "we exported it to pandas to do this bit" turns out to be one of these five.