'Get the latest timestamp from MySQL

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
mysql


Solution 1:[1]

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

Solution 2:[2]

you can try a query like this:

SELECT c.MacAddress, MAX(cc.timestamp) , r.MacAddress
FROM CmInfo c
LEFT JOIN ResetDetails r ON r.MacAddress = c.MacAddress
LEFT JOIN CmCollection cc ON cc.NodeID = c.NodeID
WHERE c.MacAddress = "1234567890";

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RF1991
Solution 2

Related Questions