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 |
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";
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