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

Asked

Viewed 57 times

1

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?

1 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

  • Thanks Eduardo, for the light! It worked here! It increased considerably the consultation time, but I’d rather have the information than lose it.

  • @Claramagalhaes unfortunately this is a disadvantage of the subconsulta. For test only, you can create an Index using the fields (code_requirement, data_low) to see if performance improves.

  • @Claramagalhaes put an alternative solution, could you say if the performance was better or worse? I would like to know with real data.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.