How select data from two column in sql?

Question

I have a table in postgresql as follow:

id |   name   | parent_id |
1  | morteza  |   null    |
2  |   ali    |   null    |
3  | morteza2 |     1     |
4  | morteza3 |     1     |

My unique data are records with id=1,2, and record id=1 modified twice. now I want to select data with last modified. Query result for above data is as follow:

id |   name   |
1  | morteza3 |
2  |   ali    |

What's the suitable query?

Answer 1

If I am following correctly, you can use distinct on and coalesce():

select distinct on (coalesce(parent_id, id)) coalesce(parent_id, id) as new_id, name
from mytable
order by coalesce(parent_id, id), id desc

Demo on DB Fiddle:

new_id | name    
-----: | :-------
     1 | morteza3
     2 | ali