Postgresql: How to select a field based on the maximum value of another field?

Question

I have a table with the following configuration:

create table baixas_do_requerimento as
codigo_requerimento text,
tipo_baixa text,
data_baixa timestamp

Each código_requerimento can repeat numerous times, ie can have several low of different types and dates.

Data examples:

requisite code	low type	low date
123abc	P	2019-03-05
123abc	P	2020-01-25
123abc	T	2020-06-15

I need to make a view where I select the codigo_requerimento, the date of the first casualty, the date of the last casualty and the type of the last casualty.

The expected result is:

requisite code	first low	last low	low type
123abc	2019-03-05	2020-06-15	T

I started as follows:

create materialized view resumo_baixas as
select
codigo_requerimento,
min(data_baixa) as primeira_baixa,
max(data_baixa) as ultima_baixa,
(...)
group by codigo_requerimento

I also need to bring in the kind of casualty of the last casualty, and I’m having a hard time figuring out a way to do that.

Some light?

Answer 1

Option 1: Sub-consultation

Do a subconsultation using the values found in the first:

SELECT
    codigo_requerimento,
    MIN(data_baixa) as primeira_baixa,
    MAX(data_baixa) as ultima_baixa,
    (SELECT tipo_baixa FROM baixas_do_requerimento WHERE codigo_requerimento = A.codigo_requerimento AND data_baixa = MAX(A.data_baixa) LIMIT 1) AS tipo_ultima_baixa
FROM
     baixas_do_requerimento A
GROUP BY
     codigo_requerimento

https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/1450

Edit - Option 2: Window Functions

Maybe this alternative using Window Functions perform better:

SELECT
    B.*
FROM
    (SELECT
        codigo_requerimento,
        MIN(data_baixa) OVER (PARTITION BY codigo_requerimento) AS primeira_baixa,
        MAX(data_baixa) OVER (PARTITION BY codigo_requerimento) AS ultima_baixa,
        FIRST_VALUE(tipo_baixa) OVER (PARTITION BY codigo_requerimento ORDER BY data_baixa DESC) AS tipo_ultima_baixa
    FROM
        baixas_do_requerimento A
    ) B
GROUP BY
    codigo_requerimento, primeira_baixa, ultima_baixa, tipo_ultima_baixa
ORDER BY
    codigo_requerimento ASC

https://www.db-fiddle.com/f/qUCE45Buh97AuAq6fFzTUH/1