I have a table like this:

treatment | patient_id
        3 |          1
        3 |          1
        3 |          1
        2 |          1
        2 |          1
        1 |          1
        2 |          2
        2 |          2
        1 |          2

I need to get only rows on max(treatment) like this:

treatment | patient_id
        3 |          1
        3 |          1
        3 |          1
        2 |          2
        2 |          2

The patient_id 1 the max(treatment) is 3 The patient_id 2 the max(treatment) is 2

CodePudding user response：

You can for example join on the aggregated table using the maximal value:

select t.*
from tmp t
inner join (
  select max(a) max_a, b
  from tmp
  group by b
) it on t.a = it.max_a and t.b = it.b;

Here's the db fiddle.

CodePudding user response：

Try this :

WITH list AS
( SELECT patient_id, max(treatment) AS treatment_max
    FROM your_table
   GROUP BY patient_id
)
SELECT *
  FROM your_table AS t
 INNER JOIN list AS l
    ON t.patient_id = l.patient_id
   AND t.treatment = l.treatment_max

CodePudding user response：

You can use rank:

with u as
(select *, rank() over(partition by patient_id order by treatment desc) r
from table_name)
select treatment, patient_id
from u
where r = 1;

Fiddle

CodePudding user response：

use corelated subquery

select t1.* from table_name t1
where t1.treatment=( select max(treatment) from table_name t2 where t1.patient_id=t2.patient_id
)