How to group rows while filtering out values per each group? Imagine you have the following setup:

CREATE TABLE namechanges (
  id text,
  new_name text,
  change_date timestamp
);

INSERT INTO namechanges VALUES
  ('x', 'alice', '2020-03-01'),
  ('y', 'Bob T.', '2020-03-03'),
  ('x', 'Alice', '2020-03-05'),
  ('z', 'charlie', '2020-03-07'),
  ('x', 'Alice C.', '2020-03-09'),
  ('z', 'Charlie Z.', '2020-03-11')


How would look like a query that would retrieve just the current name for each id and return the following?

| id  | max        |
| --- | ---------- |
| z   | Charlie Z. |
| y   | Bob T.     |
| x   | Alice C.   |


Here's the example above on DB Fiddle if you want to play with it: https://www.db-fiddle.com/f/ugNcXhRyb44KTpqjFXmKDW/0
In Postgres this can be solved using distinct on () 

select distinct on (id) *
from namechanges
order by id, change_date desc;


This is typically faster than using aggregation or Window functions.

Online example