SQL get minimum difference based on group by [duplicate]

Question

I have the following data. I want to get the record of a person_id that has the lowest_diff

person_id tab_id date_of_tab   diff
101       567    2019-01-23    0
101       509    2019-02-20    19
102       202    2019-03-01    88
102       222    2019-01-12    10

How can I get the following expected output in PSQL?

person_id tab_id date_of_tab   diff
101       567    2019-01-23    0
102       222    2019-01-12    10

Answer 1

It appears you need a solution using row_number:

with md as (
  select *,
    Row_Number() over(partition by person_id order by diff) rn
  from t
)
select person_id, tab_id, date_of_tab, diff
from md
where rn = 1;

Answer 2

use corelated subquery

select t1.* from table_name t1
where diff=( select min(diff) table_name t2 where t1.persion_id=t2.persion_id)