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

Asked

Viewed 33 times

0

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?

2 answers

0

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);
  • Thanks for the answer, I currently use this method that you mentioned, but it is a bit heavy and the more data in the table the heavier it gets. I’m looking for a more efficient alternative, so I don’t need to compare the historical id_with all the data in the table through the IN ().

  • basically I’m trying to avoid sub select

0

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/

  • Thank you for the answer, I am looking for something in this same way. I will test this solution and if it works out for me I inform.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.