How to restrict some BD iterations?

Asked

Viewed 84 times

2

Explaining the title:

I have a table called projects, these projects can be approved or disapproved.

Each insertion or update of a record in the project table is saved in a history table (e.g.: I recorded a project and then updated it 2 times / In the history table we will have 3 tuples).

Usually these are approved and flunked several times, and very rarely after approved can be flunked again.

My goal is to make a query that shows only the ID’s that have the last registration date with the situation flunked.

That is, if I have a project that has failed, failed and failed.

He should show up at the appointment.

If he failed, approved and disapproved.

It should also appear in the query.

postgresql

1 answer

0

Considering the table

idHistorico int PRIMARY AI
idProjeto int 
situacao varchar(255)

Suffice:

SELECT maxh.id, h1.idProjeto
FROM historico_projetos h1
INNER JOIN (
    SELECT MAX(idHistorico) AS id
    FROM historico_projetos
    GROUP BY idProjeto
    ) maxh 
ON maxh.id = h1.idHistorico AND h1.situacao = 'reprovado'

The SELECT internal to the INNER JOIN (maxh) returns the last idHistorico of each project (only one tuple because the projects are grouped by GROUP BY).

The condition of INNER JOIN (ON) makes the intersection based on the last idHistoric (maxh) of each group of idProjeto with the first select (h1).

The First SELECT (h1) selects all rows in the table.

This intersection (ON) selects all lines containing the last idHistorico (maxh.id) and the situation (h1.situacao = 'reprovado') select (h1).

And is returned to the code o maxh.id (greater idHistorico of the grouping of idProjeto) and the h1.idProjeto.

It’s a little tricky, really, and I believe you may have other ways of working things out.

I hope you helped him. Good luck.

Browser other questions tagged postgresql

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