I created a VIEW to return the main data I need in a single query.
CREATE
ALGORITHM = UNDEFINED
DEFINER = 'root'@'localhost'
SQL SECURITY DEFINER
VIEW v_historicoProcesso AS
SELECT p.protocolo AS protocolo
,p.der AS der
,p.data_habilitacao AS dhab
,a.descricao AS desandamento u.nome AS nome
,ap.bol_ativo AS ativo
FROM (((processo p JOIN andamento_processo ap
ON((p.id = ap.id_processo))) JOIN usuario u
ON((u.id = p.id_usuario))) JOIN andamento a
ON((ap.id_andamento = a.id)))
ORDER BY ap.data DESC
So far the return has been satisfactory. Having the result something like:
123 2014-04-01 2014-02-02 agreed simao da silva S
123 2014-04-01 2014-02-02 agreed simao da silva N
123 2014-04-01 2013-02-02 agreed simao da silva N
456 2014-04-01 2014-02-02 without agreement jose da silva N
456 2014-04-01 2014-02-02 exigencia jose da silva S
456 2014-04-01 2014-02-02 input jose da silva N
I then made the following query:
SELECT protocolo
,der
,dhab
,desandamento
,nome
FROM v_historicoprocesso
WHERE ativo = 'S'
GROUP BY idprocesso
ORDER BY der DESC
,protocolo ASC;
The result would be only the 'S' assets of each idProcess
123 2014-04-01 2014-02-02 agreed simao da silva S
456 2014-04-01 2014-02-02 exigencia jose da silva S
This query returned the following error:
Expression # 4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'v_historicoProcesso.desAndamento' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode = only_full_group_by
What is this error about? And what would be the best way to treat this query ?