CREATE TABLE MESSAGES (
    UUID            SYS$UUID NOT NULL /* SYS$UUID = CHAR(16) */,
    ON_UUID         SYS$UUID /* SYS$UUID = CHAR(16) */,
    TICK            SYS$TIMETICK NOT NULL /* SYS$TIMETICK = BIGINT */,
    MSG_TIME        SYS$TIMESTAMP /* SYS$TIMESTAMP = TIMESTAMP */,
    MSG_LOCAL_TIME  COMPUTED BY (dateadd(hour, RDB$GET_CONTEXT('USER_SESSION', 'TIMEZONE'), MSG_TIME)),
    SITE            SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    DEVICE          SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    MSG_TYPE        SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    MSG_KIND        SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    JSON            SYS$JSON /* SYS$JSON = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */
);

ALTER TABLE MESSAGES ADD CONSTRAINT MESSAGES PRIMARY KEY (UUID);
CREATE INDEX MESSAGES_TICK ON MESSAGES (TICK, DEVICE, MSG_TYPE, MSG_KIND);
CREATE INDEX MESSAGES_TIME ON MESSAGES (MSG_TIME, DEVICE, MSG_TYPE, MSG_KIND);
CREATE DESCENDING INDEX MESSAGES_TIME_DESC ON MESSAGES (MSG_TIME, DEVICE, MSG_TYPE, MSG_KIND);

with MDATA as (
  select UUID, DEVICE, MSG_TIME, MSG_TYPE, MSG_KIND, first_value(UUID) over (partition by DEVICE order by MSG_TIME desc) as OVER_UUID
  from MESSAGES
  where MSG_TIME between current_timestamp - 1 and current_timestamp
  order by DEVICE
)
select DEVICE, MSG_TIME, MSG_TYPE, MSG_KIND
from MDATA
where UUID = OVER_UUID

PLAN SORT (SORT (MDATA MESSAGES INDEX (MESSAGES_TIME)))

CREATE GLOBAL TEMPORARY TABLE LAST_DEV_MESSAGE (
    DEVICE    SYS$NAME NOT NULL /* SYS$NAME = VARCHAR(128) */,
    UUID      SYS$UUID /* SYS$UUID = CHAR(16) */,
    ON_UUID   SYS$UUID /* SYS$UUID = CHAR(16) */,
    TICK      SYS$TIMETICK /* SYS$TIMETICK = BIGINT */,
    MSG_TIME  SYS$TIMESTAMP /* SYS$TIMESTAMP = TIMESTAMP */,
    SITE      SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    MSG_TYPE  SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    MSG_KIND  SYS$NAME /* SYS$NAME = VARCHAR(128) */,
    JSON      SYS$JSON /* SYS$JSON = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */
) ON COMMIT PRESERVE ROWS;
ALTER TABLE LAST_DEV_MESSAGE ADD PRIMARY KEY (DEVICE);

execute block
returns (
    DEVICE type of column LAST_DEV_MESSAGE.DEVICE,
    UUID type of column LAST_DEV_MESSAGE.UUID,
    ON_UUID type of column LAST_DEV_MESSAGE.ON_UUID,
    TICK type of column LAST_DEV_MESSAGE.TICK,
    MSG_TIME type of column LAST_DEV_MESSAGE.MSG_TIME,
    SITE type of column LAST_DEV_MESSAGE.SITE,
    MSG_TYPE type of column LAST_DEV_MESSAGE.MSG_TYPE,
    MSG_KIND type of column LAST_DEV_MESSAGE.MSG_KIND,
    JSON type of column LAST_DEV_MESSAGE.JSON
)
as
begin
  delete from LAST_DEV_MESSAGE;
  for select m.DEVICE, m.UUID, m.ON_UUID, m.TICK, m.MSG_TIME, m.SITE, m.MSG_TYPE, m.MSG_KIND, m.JSON
      from MESSAGES m
      where m.MSG_TIME between current_timestamp - 1 and current_timestamp
      order by m.MSG_TIME desc
      into :DEVICE, :UUID, :ON_UUID, :TICK, :MSG_TIME, :SITE, :MSG_TYPE, :MSG_KIND, :JSON
   do begin
      if (exists (select * from LAST_DEV_MESSAGE where DEVICE = :DEVICE))
         then continue;
      insert into LAST_DEV_MESSAGE (DEVICE, UUID, ON_UUID, TICK, MSG_TIME, SITE, MSG_TYPE, MSG_KIND, JSON)
      values (:DEVICE, :UUID, :ON_UUID, :TICK, :MSG_TIME, :SITE, :MSG_TYPE, :MSG_KIND, :JSON);
      suspend;
      end
end

WITH T AS (
  SELECT MAX(m.TICK) as TICK
  FROM MESSAGES m
  WHERE MSG_TIME between current_timestamp - 30 and current_timestamp
  GROUP BY DEVICE)
SELECT
  MESSAGES.DEVICE, MESSAGES.MSG_TIME, MESSAGES.MSG_TYPE, MESSAGES.MSG_KIND
FROM T
JOIN MESSAGES ON T.TICK = MESSAGES.TICK
ORDER BY MESSAGES.DEVICE