SQL – Column containing current firmware string based on occational messages with several machines

I have a table called SessionEvents.

I’m trying to create a column that represents the firmware that is on the machine to later be able to filter messages for only certain software.

Currently my SQL query looks like this. (Based on answer by adamlamar in How do I efficiently select the previous non-null value? with slight modifications.)

I’m using JOIN in since machine_id is in a related table called Sessions.

SELECT *,
    first_value(message_info) over (partition by value_partition order by event_time) as ColumnFirmware

FROM(        
SELECT SessionEvents.event_time, SessionEvents.message_type, SessionEvents.message_info, Sessions.machine_id,
    sum(case when message_type <> 1 then 0 else 1 end) over (partition by machine_id order by event_time) as value_partition

FROM SessionEvents
JOIN Sessions ON SessionEvents.SessionId=Sessions.Id) t
-- WHERE machine_id = 1

This works for a single machine but i cant find a way to implement this properly for several machines. How should i modify the query to adopt if for several machines?

(Data is fetched from Azure SQL database)

Answer

This is a gaps-and-islands problem. First define the islands, then spread the firmware:

SELECT s.*,
       MAX(CASE WHEN message_type = 1 THEN message_info
           END) OVER (PARTITION BY machine_id, seqnum - sequm_m
                     ) as firmware
FROM (SELECT se.event_time, se.message_type, se.message_info, s.machine_id,
             ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY event_time) as seqnum_m,
             ROW_NUMBER() OVER (ORDER BY event_time) as seqnum
      FROM SessionEvents se JOIN
           Sessions s
           ON se.SessionId = s.Id
      ) s