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?

    I think you can do it this way:

    WHERE (...) AND faultevents.ID = MAX(faultevents.ID)

    ...or maybe:

    WHERE (...) AND faultevents.ID = SELECT MAX(faultevents.ID) FROM faults

      Thanks for the suggestions. When entering the revised query into phpMyAdmin (vX.X) I get error messages.

      Solution 1 gave "#1111 - Invalid use of group function "

      Solution 2 gave "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM faults' at line 7 "

        Just for reference, I am running phpMyAdmin 2.6.1 and MySQL 4.1.10-nt

          select faultevents.MAATNum, 
                 faultevents.DateD, 
                 faultevents.DateM, 
                 faultevents.DateY, 
                 faultevents.TimeH, 
                 faultevents.TimeM, 
                 faultevents.Event, 
                 faultevents.ID
            from faults
            join faultevents 
              on faults.MAATNum = faultevents.MAATNum
            join Events 
              on faultevents.EventType = events.id
            join customercontacts_admin 
              on customercontacts_admin.CustomerNum = faults.Customer
           where faults.type = 1 
             and faults.Open = 0
             and faultevents.ID in
                (select max(ID)
                   from faultevents q
                  where q.MAATNum = faultevents.MAATNum)
          

            Awesome Lars, that seems to have got it. Many, many thanks.

              You can mark this thread Resolved by using the link under the Thread Tools menu above.

                Write a Reply...