How to group rows while filtering out values per each group?
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