Get only rows where data is the max value

Question

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

Italo Rodrigo · Accepted Answer · 2022-03-03 15:02:59Z

1

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.

edited Mar 3, 2022 at 15:02

Italo Rodrigo

1,7855 gold badges20 silver badges42 bronze badges

answered Mar 3, 2022 at 13:53

Andronicus

26.2k18 gold badges55 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edouard · Accepted Answer · 2022-03-03 13:55:31Z

1

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

answered Mar 3, 2022 at 13:55

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Comments

Zakaria · Accepted Answer · 2022-03-03 13:56:50Z

1

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

answered Mar 3, 2022 at 13:56

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

Comments

Zaynul Abadin Tuhin · Accepted Answer · 2022-03-03 14:08:56Z

0

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
)

answered Mar 3, 2022 at 14:08

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Get only rows where data is the max value

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related