Get latest timestamp for each category

Question

_{Monday, January 21, 2019 2:06 AM}

Hi,

How do I query to get latest timestamp per category, this to get a summary per category (sensorId in this example) with the latest timestamp for each. 

Similar to this in SQL:

SELECT sensorID,timestamp,sensorField1,sensorField2

FROM sensorTable s1
WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable s2 WHERE s1.sensorID = s2.sensorID)
GROUP BY sensorID;

That would give something like this:

| SensorID | Timestamp | SensorField1 | SensorField2 |

| 123         | 2018-01-01| 13               | 80                |
| 124         | 2018-03-03| 7                 | 52                |
| 125         | 2018-05-04| 10               | 32                |
| 126         | 2018-07-22| 12               | 88                |
| 127         | 2018-11-21| 19               | 70                |

Best Regards
-Anders
ps. expecting something really simple, as Log Analytics Query is fantastic. :) .ds

All replies (2)

_{Monday, January 21, 2019 4:03 PM ✅Answered}

Hi,

I think what you need is arg_max() operator:

https://docs.microsoft.com/bs-cyrl-ba/azure/kusto/query/arg-max-aggfunction

and something like:

Table1 | summarize arg_max(Timestamp, *) by SensorId;

Mark this reply as answer if it has helped you.

---

_{Monday, January 21, 2019 3:39 AM}

Maybe too quick to ask the question.. .sorry..
I can solve it by doing:

let latestRecords = Table1 | summarize Timestamp=max(Timestamp) by SensorId;
Table1| join latestRecords on Timestamp
| project SensorId, SensorField1, SensorField1, Timestamp
| order by Timestamp

...maybe there is a smarter way to do this in Log Analytics Query language?

Regards
-Anders