How to return only the last maintenance line of each equipment?

Asked

Viewed 56 times

0

I’m implementing the report module in a system I’m developing, but I can’t get where I want, my scenario:

I have an equipment table, linked by foreign key to another table called maintenance, in the maintenance table I register only the date of maintenance and connect to the desired equipment

When I try to return one query for the report, if there are two maintenance dates, it returns two Rows of the same equipment, changing only the date of maintenance...

I wanted to return only the date of ULTIMA maintenance, but I’m not getting.

I am using the following query:

SELECT * FROM equipamento 
INNER JOIN manutencao ON id_equipamento = id_equipamento_manutencao

Print how you are returning in the report:

screenshot

php mysql sql report

  • I understand "last" as the latest date, use the GROUP BY clause and the MAX aggregation function: SELECT equipamento.id_equipamento, max(manutencao.data) FROM equipamento 
INNER JOIN manutencao ON id_equipamento = id_equipamento_manutencao
GROUP BY equipamento.id_equipamento.

  • 1

    Anyway, you should search here on the site, because this question has already been made in some different ways. If none serves, you can [Dit] with a [mcve] (structure - only of the relevant fields - example data, intended result and result obtained with this data) It is worth reading the Stack Overflow Survival Guide in English to better understand the site and how to formulate it. Taking the opportunity to exchange the image for text - I’m trying to locate similar problem to indicate link.

  • 1

    About the "You close the question at the speed of light, to hinder you are quick, to help you run away." @Matheus' answer is a good example of why we close while details are missing. Came out a lot of complication (and does not solve duplicity, ie is not help) so far. We are helping the author and the community to ensure quality, which is the goal of the site. Read the Guide indicated in the previous comment to better understand.

  • Luis, I think this might help, it’s a similar case (different symptom, but same solution) and an explanation https://answall.com/questions/422495/

  • Thank you all very much for the answer, I managed to solve my problem !

2 answers

0


The main flaw is not identifying where the variables in the ON clause come from. Here I identify the tables by a letter and link the variables to the respective table. I also changed from INNER to LEFT JOIN because all maintenance should be linked to an equipment. But if you want to maintain maintenance without tied equipment your query is not wrong.

SELECT 
    E.ID,
    MAX(M.data)
FROM 
    equipamento as E
    LEFT JOIN manutencao as M ON E.id_equipamento = M.id_equipamento_manutencao 
GROUP BY
    E.id_equipamento

0

You can use a CTE to select only the last maintenance and then use in JOIN, it gets more semantic.

Example (remember to fit existing tables):

Here a sqlfiddle:

with ultima_manutencao as 
(
  select 
     max(m.data_manutencao) data_ultima_manut
     , m.equip_id
  from 
    manut m
  group by 
    m.equip_id
)
select 
  * 
from 
  equip e
  join ultima_manutencao um on um.equip_id = e.id

Browser other questions tagged php mysql sql report

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