Postgres: Distinct but only for one column and latest one

Question

I have the following original table

What I want is to retrieve latest history_id with distinct new_id. As follow:

I found this distinct_but_only_one_column but it only contains how to retrieve distinct one column. Please help me out.

Answer 1

This will work, assuming your table is named x:

select * from x
where history_id in (
   select max(history_id) from x group by news_id
);

Output:

 history_id | news_id |      news_headline       
------------+---------+--------------------
          1 |       5 | My test Headline
         13 |       7 | My test Headline
          3 |       4 | New Headline
          4 |       6 | History Headline

This also works (and is simpler):

select distinct on (news_id) *
from x
order by news_id, history_id desc;

(but this never feels "right" to me)