Error in Mysql query

Question

Hello! this doubt arose in mysql query;

because the status is coming different from the table?

follow my consultation:

SELECT DISTINCT max(idEmbreagem) as idEmbreagem, b.prefixo as idVeiculo, max(datamontagem) as datamontagem, max(kmmontagem) as kmmontagem, max(horimetromontagem) as horimetromontagem, max(c.km) as kmatual, max(c.horimetro) as horimetroatual, a.`status`, max(tipoembreagem) as tipoembreagem, (max(c.km) - max(a.kmmontagem)) as kmrodados FROM embreagem a
              JOIN veiculos b on (a.idVeiculo=b.idVeiculo)
              JOIN hodometro c on (a.idVeiculo=c.idVeiculo)
              GROUP BY a.idVeiculo order by idVeiculo ASC

Answer 1

Buddy, ditinct with max... something feels really bad there... =/

The fact is that the distinct and the MAX()’s are messing up the results, shuffling them... You should think about building this query.

Answer 2

Because you are bringing several max in the query, it ta shuffling the results with max of each select following the conditional.

Answer 3

If you want to get the latest data from the tables embreagem and hodometro, do so:

SELECT b.idembreagem, a.prefixo idveiculo, a.datamontagem,
       a.kmmontagem, a.horimetromontagem, c.km kmatual,
       c.horimetro horimetroatual, b.status, b.tipoembreagem,
       (c.km - a.kmmontagem) as kmrodados
FROM veiculos a
JOIN embreagem b ON b.idveiculo = a.idveiculo
JOIN hodometro c ON c.idveiculo = a.idveiculo
WHERE b.idembreagem IN (SELECT MAX(idembreagem) FROM embreagem GROUP BY idveiculo)
AND c.idhodometro IN (SELECT MAX(idhodometro) FROM hodometro GROUP BY idveiculo)
ORDER BY a.prefixo ASC