I have a query that returns many rows from a database of faults. What I am trying to achieve is to build a report that details tha last event in a fault record.
My initial query (see below) simply returns all the records for event fault (MAATNum) which is the primary key in tables faults and a regular column in faultevents.
SELECT faultevents.MAATNum, faultevents.DateD, faultevents.DateM, faultevents.DateY, faultevents.TimeH, faultevents.TimeM, faultevents.Event, faultevents.ID
FROM faults
INNER JOIN faultevents
USING (MAATNum)
INNER JOIN Events ON faultevents.EventType = events.id
INNER JOIN customercontacts_admin ON customercontacts_admin.CustomerNum = faults.Customer
WHERE faults.type = 1 AND faults.Open = 0
after ending the query with:
GROUP BY faultevents.MAATNum
The results appear to select the a random line which is mainly the first entry.
When amending the first line to
SELECT faultevents.MAATNum, faultevents.DateD, faultevents.DateM, faultevents.DateY, faultevents.TimeH, faultevents.TimeM, faultevents.Event, MAX(faultevents.ID)
The results are almost identical to simply adding the GROUP BY line the except the field "faultevents.ID" does contain the highest number ID but not the data from that line of the database.
The database is one that I am unable to change the structure of and I am pretty sure that it has not been normalised.
Has anyone got any tips on how to achieve my goals?