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

Table 1: CmInfo

Table 2: ResetDetails

Tabl3 3: CmCollection

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

CodePudding user response：

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  

CodePudding user response：

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 CmInfo = "1234567890";