How to restrict some iterations of DB?

Question

How to restrict some iterations of DB?

Navigation

#1 by (0 votes)

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 stored in a history table (eg I saved a project and then updated 2 times / In the history table we will have 3 tuples).

Normally these are approved and disapproved several times, and very rarely after approval can be disapproved again.

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

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

It should appear in the query.

If it was disapproved, approved, and disapproved.

It should also appear in the query.

postgresql

asked by anonymous 13.01.2015 / 20:34

1 answer

Object request json always returning NULL in php Custom Menu on site

score 0 · Answer 1

Considering the table

idHistorico int PRIMARY AI
idProjeto int 
situacao varchar(255)

Enough:

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 of INNER JOIN ( maxh ) returns the last idHistorico of each project (only one tuple since the projects are grouped by GROUP BY ).

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

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

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

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

It's a little tricky, and I think there might be other ways to resolve it.

I hope to help you. good luck.