Versioned table with grouping by updated_at

localities.sql

CREATE TABLE localities (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  source_updated_at TIMESTAMP,
  name TEXT
);
INSERT INTO localities (name, source_updated_at) VALUES
  ('London', '2023-04-01'),
  ('London', '2023-03-15'),
  ('Paris', '2023-02-11'),
  ('Canberra', '2022-10-05');

WITH most_recent_ids AS (
  SELECT DISTINCT(FIRST_VALUE(id) OVER (PARTITION BY name ORDER BY source_updated_at DESC)) AS id FROM localities
) SELECT * FROM localities INNER JOIN most_recent_ids ON most_recent_ids.id = localities.id;