Get maximum value and most recent from a column

I have a table that has the following structure:

id     blood_pressure    created

So for every id, I want to get the : highest blood_pressure (max(blood_pressure), as well as the latest blood_pressure created, in one query. I need:

 id     max(blood_pressure). latest(blood_pressure value)

How can I write this query in PostgreSQL ?

CodePudding user response：

You can use window functions to find the max per group and latest per group, such as:

with p as (
    select *,
      Max(pressure) over(partition by Id) HighestPressure,
      Row_Number() over(partition by Id order by Created desc) rn
    from t
)
select Id, Pressure, HighestPressure
from p
where rn=1;

*Untested of course with no sample data to use.

CodePudding user response：

You can use distinct on to get the latest pressure, and the max window function to get the highest:

select distinct on(id) id,
max(blood_pressure) over(partition by id) as max_pressure,
blood_pressure as latest_pressure
from table_name
order by id, created desc;