How to search multiple columns together with max() SQL (Mysql)

Question

The problem is this, I have the tables protocolo and historico; second stores several records linked to a record of the protocol table. I wanted to fetch the last record of each historical for protocol, but along with the rest of the fields in the historical, for example

SELECT MAX(id_historico), id_local, id_situacao, id_protocolo 
FROM historico 
GROUP BY id_protocolo

The problem is that when I do this query it returns the id_histotico the maximum correctly, but the rest of the fields are not from the same line as the id_historico, is returning other wrong fields. Could someone help me understand and resolve this?

Answer 1

You can make a subconsultation to first load the id_historico which will be displayed; with this, you can click on select the data (id_local and id_situation) of that specific item, restricting that in where:

SELECT id_historico, id_local, id_situacao, id_protocolo
FROM historico
WHERE id_historicoIN (SELECT MAX(id_historico) FROM historico GROUP BY id_protocolo);

Answer 2

Another opium, if you’re using mysql version 8+, is to use ROW_NUMBER() OVER, to generate an "order" number for each history, based on the id_protocol:

WITH UltimoHistorico AS (
  SELECT h.*, ROW_NUMBER() OVER (PARTITION BY id_protocolo ORDER BY id_historico DESC) AS id_ordem
  FROM historico AS h
)
SELECT * FROM UltimoHistorico WHERE id_ordem = 1;

Basically, it generates a numerical sequence, in descending order, based on the id_protocol, that is, this number when it is "1", is the last

A working example can be seen here: https://www.db-fiddle.com/