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?
Thanks Eduardo, for the light! It worked here! It increased considerably the consultation time, but I’d rather have the information than lose it.
– Clara Magalhaes
@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.
– Eduardo Bissi
@Claramagalhaes put an alternative solution, could you say if the performance was better or worse? I would like to know with real data.
– Eduardo Bissi