-1

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
Eisen
  • 1,697
  • 9
  • 27
  • 1
    There are many soltutions to this (rank the rows with row_number, get the minimum with a window function, use an in expression with tuples, see that not exists a lesser value, use a fetch with ties clause, ...). But it depends on your DBMS what it features. What is your DBMS? Please tag your request with it. – Thorsten Kettner Feb 21 '22 at 18:17
  • Which dbms are you using? – jarlh Feb 21 '22 at 18:18
  • Then you must define what to do in case of ties. What if there is another row for person 101 with a diff of 0? Select both rows? No rows? Only one of them? Which? – Thorsten Kettner Feb 21 '22 at 18:18

2 Answers2

1

use corelated subquery

select t1.* from table_name t1
where diff=( select min(diff) table_name t2 where t1.persion_id=t2.persion_id)
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
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;
Stu
  • 30,392
  • 6
  • 14
  • 33