'Get maximum value and most recent from a column - PostgreSQL

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 ?

sqlpostgresql


Solution 1:[1]

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.

Solution 2:[2]

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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Zakaria

Related Questions