I have a problem when performing an SQL using a temporary table .
I need to bring the data da ultima alteração
into which the opportunity has changed.
However, this date comes from several tables for the same opportunity. An opportunity is the header of a request, below it there are other tables that are actions that the opportunity suffered, so the tables are as follows:
- opportunity
- opportunity_action
- opportunity_proposal
- [...]
I searched the data for all the tables and brought the date of the last change (timestamp_timestamp) of the opportunities.
Example records:
oportunidade_id | alteracao_timestamp | tela
3024 | 2015-07-03 00:00:00 | oportunidade
3024 | 2018-02-15 16:18:02 | oportunidade_acao
10930 | 2017-01-05 00:00:00 | oportunidade
10930 | 2018-02-15 16:25:08 | oportunidade_acao
16104 | 2017-05-10 00:00:00 | oportunidade
16104 | 2017-10-03 11:06:00 | oportunidade_acao
16104 | 2017-05-10 00:00:00 | oportunidade_proposta
16104 | 2017-05-26 11:51:00 | oportunidade_compromisso
In this example you can verify that the last change of all opportunities is in the opportunity_account table.
Now transferring this to SQL:
select
a.*
from alteracoes_oportunidades a
inner join (
select oportunidade_id, max(alteracao_timestamp) ultima_alteracao
from alteracoes_oportunidades
group by oportunidade_id
) b on b.ultima_alteracao = a.alteracao_timestamp
and a.oportunidade_id = b.oportunidade_id
Being alteracoes_oportunidades
a temporary table, is generating the error:
SQL Error (1137): Can not reopen table: 'a'
I've noticed the MySQL documentation that you can not use any more of 1x a temporary table in a SQL. Otherwise the above SQL would solve my problem .
So I had to rewrite SQL to:
select a.oportunidade_id, a.tela, max(a.alteracao_timestamp) as alteracao_timestamp
from alteracoes_oportunidades a
group by a.oportunidade_id
But this returns me to incorrect information, it is returning me:
oportunidade_id | tela | alteracao_timestamp
3024 | oportunidade | 2018-02-15 16:18:02
10930 | oportunidade | 2018-02-15 16:25:08
16104 | oportunidade | 2017-10-03 11:06:00
What I need is for me to get back to the biggest change date and on what screen was the change . So the result should be:
oportunidade_id | tela | alteracao_timestamp
3024 | oportunidade_acao | 2018-02-15 16:18:02
10930 | oportunidade_acao | 2018-02-15 16:25:08
16104 | oportunidade_acao | 2017-10-03 11:06:00
What would be the correct way to accomplish SQL using the temporary table?