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

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.

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

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

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

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

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

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 )

Collectives™ on Stack Overflow

Get only rows where data is the max value

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related