ROW_NUMBER()

ROW_NUMBER() is a window function that assigns a unique, sequential integer to each row within an ordered partition — never ties, unlike RANK() or DENSE_RANK().

It's the standard tool for deduplication (keep WHERE rn = 1 per group) and pagination (fetch rows where rn BETWEEN 21 AND 30 for "page 3"). Because every row gets a distinct number even when values tie, which of the tied rows gets which number depends on the tiebreaker you include in ORDER BY — without one, the order among ties is not guaranteed.

-- Keep only the most recent row per employee
SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY updated_at DESC) AS rn
  FROM employee_history e
) WHERE rn = 1;