[SOLVED] Get the latest timestamp from MySQL

Issue

Hi I have a query that requires to get the latest timestamp. Below are my tables. Thanks in advance.

Table 1: CmInfo

MacAddress NodeID
1234567890 1234

Table 2: ResetDetails

MacAddress ResetReason
1234567890 Reboot

Tabl3 3: CmCollection

timestamp NodeID
2022-03-20 11:00 1234
2022-03-20 11:10 1234
2022-03-20 11:15 1234

Required output: Just get the latest timestamp from table3 and join with table1 and 2.

Timestamp MacAddress ResetReason
2022-03-20 11:15 1234567890 Reboot

Solution

Use following query

SELECT timestamp,
       CI.macaddress,
       resetreason
FROM   (SELECT nodeid,
               Max(timestamp) timestamp
        FROM   cmcollection
        GROUP  BY nodeid) CC
       JOIN cminfo CI
         ON CC.nodeid = CI.nodeid
       JOIN resetdetails RD
         ON RD.macaddress = CI.macaddress  

Answered By – RF1991

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.