CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Italo Rodrigo
Italo Rodrigo

Reputation: 1785

Get only rows where data is the max value

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

Upvotes: 0

Views: 196

Answers (4)

Andronicus
Andronicus

Reputation: 26026

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.

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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
)

Upvotes: 0

Zakaria
Zakaria

Reputation: 4796

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

Upvotes: 1

Edouard
Edouard

Reputation: 7065

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

Upvotes: 1

Related Questions