Return maximum table value in JOIN - MYSQL

Question

I need to return the last record of a table that is on JOIN of my consultation.

But my knowledge is limited and I can’t go on:

Table Services:

Table situation:

Result of my query:

My consultation

SELECT s.id_servico,
       s.titulo,
       s.valor,
       st.situacao,
       st.id_situacao
FROM   servicos s
       JOIN (SELECT id_servico,
                    Max(id_situacao) AS id_situacao,
                    situacao
             FROM   situacao
             GROUP  BY 1) st
         ON st.id_servico = s.id_servico
WHERE  s.id_servico = 2872

Answer 1

Your query was almost ok. Note that to make the query work you need two criteria in Join:

• servicos.id_servico = situacao.id_servico: first Join, who connects the tables
• situacao.id_situacao = max(situacao.id_situacao): to take the "last" record, using the max.

This second JOIN is done in the subquery, which needs to catch the MAX(id_suituacao), but needs to be grouped by id_servico, ie, "always bring the last id_situation for each id_servico".

It can be done like this:

SELECT s.id_servico, 
       s.titulo, 
       s.valor, 
       st.situacao, 
       st.id_situacao 
  FROM servicos s 
 INNER JOIN situacao st ON st.id_servico = s.id_servico
 INNER JOIN (
     select max(id_situacao) id_situacao
       from situacao
      group by id_servico) si
       ON st.id_situacao = si.id_situacao
 WHERE s.id_servico=2872;

You can see it working here: http://sqlfiddle.com/#! 9/d61e80/18

In short, the second subquery JOIN takes the largest id_situation by id_servico, and does JOIN with the "situation" table itself, which has the alias "st"