Question

A data engineer has a Delta table `order_events` with columns `order_id`, `event_ts`, and `status`. Each order can have many rows as its status changes over time. The engineer needs to produce a result set containing exactly one row per `order_id` showing its most recent status (based on `event_ts`). Which Spark SQL approach correctly returns only the latest status per order?

Accepted Answer

Use `ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY event_ts DESC)` in a subquery, then filter the outer query to rows where the row number equals 1.

Answer

Use `GROUP BY order_id` and select `status` directly along with `MAX(event_ts)`.

Answer

Use `RANK() OVER (ORDER BY event_ts DESC)` without a PARTITION BY clause, then keep rows where rank equals 1.

Answer

Use `SELECT DISTINCT order_id, status` to collapse duplicate rows into one per order.