Query to get the latest treatment for each machine

Let me just start by saying that I don’t care on which type of SQL I get an answer on. In reality I am creating my question in Kusto but the Kusto thread in stackoverflow is dead most of the time. This is just to give me an idea of on which way I could do this so I can then translate it somehow into Kusto.

I have a database called “MachineData” that looks something like this (but with hundreds of thousands of records)

What I want to do is get for each Machine the latest treatment that the machine has done. In other words, I want for each machine to get the most recent StartTime.

I thought about doing something where I say “Order by SerialNumber, StartTime” but because there are hundreds of thousands of records then my system can’t do that without crashing because of all the amount of data there is, and also this approach will still show me all records for each Machine and what I want to do is just get the latest StartTime.

The other thing I thought about doing is something like this,

MachineData
| top 1 by SerialNumber, StartTime

but the “top” command on Kusto only accepts one parameter to order by.

Answer

Probably you’re looking for GROUP BY and max():

SELECT SerialNumber, max(StartTime) as MostRecentStartTime 
  FROM MachineData
 GROUP BY SerialNumber;