Selecting the first row of group with additional group by columns

Question

Say I have a table with the following results:

How is it possible for me to select such that I only want distinct parent_ids with the min result of object0_behaviour?

Expected output:

parent_id | id | object0_behaviour | type
------------------------------------------
1         | 1  | 5                 | IP
2         | 3  | 5                 | IP
3         | 5  | 7                 | ID
4         | 6  | 7                 | ID
5         | 8  | 5                 | IP
6         | 18 | 7                 | ID
7         | 10 | 7                 | ID
8         | 9  | 5                 | IP

I have tried:

SELECT parent_id, min(object0_behaviour) FROM table GROUP BY parent_id

It works, however if I wanted the other 2 additional columns, I am required to add into GROUP BY clause and things go back to square one.

I saw examples with R : Select the first row by group

Similar output from what I need, but I can't seem to convert it into SQL

Answer 1

You can try using row_number() window function

select * from
(
select *, row_number() over(partition by parent_id order by object0_behaviour) as rn
from tablename
)A where rn=1

Answer 2

select * from table
join (
   SELECT parent_id, min(object0_behaviour) object0_behaviour 
   FROM table GROUP BY parent_id    
) grouped 
on grouped.parent_id = table.parent_id
and grouped.object0_behaviour = table.object0_behaviour