How would you fetch the latest order, login, or event per user in SQL?

In production I confirm whether the query needs the latest row for every group or only a small requested set. Ranking an entire event history may sort or scan far more data than an indexed TOP (1) seek per selected parent, an aggregate strategy with a unique sequence, or a maintained current-state table. The best shape depends on group count, rows per group, filters, indexes, and query frequency, so I compare actual plans, logical reads, memory grants, spills, CPU, and elapsed time at realistic skew. ROW_NUMBER is nondeterministic when the partition and order columns do not produce a unique sequence; adding a stable tie-breaker is part of correctness. If late-arriving events can have older timestamps but newer business sequence, the ordering uses the authoritative sequence rather than ingestion time. A materialized latest-state read model makes reads predictable but adds write consistency, replay, and repair obligations. I validate duplicate timestamps, missing groups, tenant predicates, and concurrent inserts, then monitor read growth. The answer is not simply “use a window function”; it is define latest precisely, return one correct row, and choose an access path whose cost remains acceptable.