SQL : GROUP and MAX multiple columns

Question

I am a SQL beginner, can anyone please help me about a SQL query?

my table looks like below

PatientID   Date        Time    Temperature
1           1/10/2020   9:15    36.2
1           1/10/2020   20:00   36.5
1           2/10/2020   8:15    36.1
1           2/10/2020   18:20   36.3
2           1/10/2020   9:15    36.7
2           1/10/2020   20:00   37.5
2           2/10/2020   8:15    37.1
2           2/10/2020   18:20   37.6
3           1/10/2020   8:15    36.2
3           2/10/2020   18:20   36.3

How can I get each patient everyday's max temperature:

PatientID   Date        Temperature
1           1/10/2020   36.5
1           2/10/2020   36.3
2           1/10/2020   37.5
2           2/10/2020   37.6

Thanks in advance!

Answer 1

For this dataset, simple aggregation seems sufficient:

select patientid, date, max(temperature) temperature
from mytable
group by patientid, date

On the other hand, if there are other columns that you want to display on the row that has the maximum daily temperature, then it is different. You need some filtering; one option uses window functions:

select *
from (
    select t.*, 
        rank() over(partition by patientid, date order by temperature desc)
    from mytable t
) t
where rn = 1