Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE  "TEMPR_SILO" 
   ( 	
	"NAME" VARCHAR2(100), 
	"TEMP" NUMBER, 
	"ID_TRANS" NUMBER
	
   )  
 INSERT ALL
 
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS)  VALUES ( 'SENSOR_1',12,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENOR_2',11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_3', 13,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_4', 11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_5', 12,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_6',13,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_7', 15,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_8', 16,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_9',18,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_10',11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_11',11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_12', 13,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_14', 19,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_15', 18,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_16',17,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_17', 11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_18',11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_19', 11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_20', 13,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_21', 12,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_22', 16,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_23', 17,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_24', 11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_25',12,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_26', 13,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_27', 19,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_28', 10,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_29', 11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_30', 16,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_31',17,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES ( 'SENSOR_32',11,1)
INTO TEMPR_SILO (NAME , TEMP, ID_TRANS) VALUES (  'SENSOR_33', 11,1)
SELECT * FROM dual;
32 rows affected

CREATE TABLE  "SILO_SENSOR" 
   (	"ID" NUMBER, 
	"NAME" VARCHAR2(500), 
	"DEVICES_ID" VARCHAR2(100), 
	"CR_DATE" DATE, 
	"NAME_LVL" VARCHAR2(100), 
	"ORGANIZATIONS" NUMBER

   )
 INSERT ALL 
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('1', 'SENSOR_1', 1,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('2', 'SENSOR_2', 1,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('3', 'SENSOR_3', 1,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('4', 'SENSOR_4', 2,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('5', 'SENSOR_5', 2,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('6', 'SENSOR_6', 2,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('7', 'SENSOR_7', 3,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('8', 'SENSOR_8', 3,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('9', 'SENSOR_9', 3,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('10', 'SENSOR_10', 4,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('11', 'SENSOR_11', 4,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('12', 'SENSOR_12', 4,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('13', 'SENSOR_13', 5,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('14', 'SENSOR_14', 5,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('15', 'SENSOR_15', 5,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('16', 'SENSOR_16', 6,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('17', 'SENSOR_17', 6,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('18', 'SENSOR_18', 6,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('19', 'SENSOR_19', 7,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('20', 'SENSOR_20', 7,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('21', 'SENSOR_21', 7,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('22', 'SENSOR_22', 7,'Level_4')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('23', 'SENSOR_23', 8,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('24', 'SENSOR_24', 8,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('25', 'SENSOR_25', 8,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('26', 'SENSOR_26', 9,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('27', 'SENSOR_27', 9,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('28', 'SENSOR_28', 9,'Level_3')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('29', 'SENSOR_29', 9,'Level_4')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('30', 'SENSOR_30', 9,'Level_5')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('31', 'SENSOR_31', 10,'Level_1')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('32', 'SENSOR_32', 10,'Level_2')
INTO SILO_SENSOR (id , NAME,DEVICES_ID,NAME_LVL) VALUES ('33', 'SENSOR_33', 10,'Level_3')
SELECT * FROM dual;
33 rows affected

CREATE TABLE  "HANGINGTHREAD_SILO" 
   (	"ID" NUMBER, 
	"DEVICES_ID" NUMBER, 
	"ID_SILO" NUMBER, 
	"CR_DATE" DATE, 
	"ORGANIZATIONS" NUMBER
   )
 INSERT ALL
 INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (1, 1, 1)
  INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (2, 2, 1)
   INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (3, 3, 1)
    INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (4,4, 1)
     INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (5,5, 1)
      INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (6, 6, 2)
       INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (7, 7, 2)
        INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (8, 8, 2)
         INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (9, 9, 2)
          INTO HANGINGTHREAD_SILO (id , DEVICES_ID,ID_SILO) VALUES (10,10,2) 
SELECT * FROM dual;
10 rows affected

CREATE TABLE  "IOT_DEVICES" 
   (	"ID" NUMBER NOT NULL ENABLE, 
	"NAME" VARCHAR2(199), 
	"TYPEID" NUMBER, 
	"DS_ID" NUMBER, 
	"DEVID" VARCHAR2(100), 
	"AUTHOR" NUMBER, 
	"CR_DATE" DATE, 
	"ORGANIZATION" NUMBER, 
	"GATEWAY" NUMBER, 
	"TYPE" VARCHAR2(150), 
	"GUID" VARCHAR2(200), 
	"OUTGUID" VARCHAR2(100)
   ) 
 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (10, 'P10', 10,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (9, 'P9', 9,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (8, 'P8', 8,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (7, 'P7', 7,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (6, 'P6', 6,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (5, 'P5', 5,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (4, 'P4', 4,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (3, 'P3', 3,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (2, 'P2', 2,1);
1 rows affected

 INSERT INTO IOT_DEVICES (id , NAME,OUTGUID,ORGANIZATION) VALUES (1, 'P1', 1,1);
1 rows affected

CREATE TABLE  "SILO" 
   (	"ID" NUMBER, 
	"NAME" VARCHAR2(500), 
	"ORGANIZATION" VARCHAR2(500)
   )
CREATE TABLE  "ORGANIZATIONS" 
   (	"ID" NUMBER, 
	"NAME" VARCHAR2(500)

   )
 INSERT INTO ORGANIZATIONS (id , name ) VALUES (1, 'TEST' );
1 rows affected

 INSERT INTO SILO (id , name,ORGANIZATION) VALUES (1, 'S1', 1);
1 rows affected

  INSERT INTO SILO (id , name,ORGANIZATION) VALUES (2, 'S3', 1);
1 rows affected

 SELECT
    TYP.NAME   AS SILO_NAME,
    TR.OUTGUID,
    TR.NAME    AS DEV_NAME,
    TR.id,
    TYP.ORGANIZATION as ID_ORG,
    rt.name as ORGANIZATION,
   MAX(TS.TEMP) AS MAX_TEMP,
   MIN(NS.TEMP) AS MIN_TEMP 
FROM
    HANGINGTHREAD_SILO EV
    LEFT JOIN SILO TYP ON EV.ID_SILO = TYP.ID 
    LEFT JOIN IOT_DEVICES TR ON EV.DEVICES_ID = TR.ID  
    LEFT JOIN SILO_SENSOR SS ON SS.DEVICES_ID = TR.OUTGUID
     LEFT JOIN TEMPR_SILO TS ON TS.NAME = SS.NAME
     LEFT JOIN TEMPR_SILO NS ON NS.NAME = SS.NAME
    LEFT JOIN ORGANIZATIONS rt ON rt.id = TR.ORGANIZATION  


where TS.ID_TRANS IN (
        SELECT
            MAX(ID_TRANS)
        FROM
            TEMPR_SILO 
    ) and NS.ID_TRANS IN (
        SELECT
            MAX(ID_TRANS)
        FROM
            TEMPR_SILO 
    )

GROUP BY
    EV.ID,
    TYP.NAME,
    TR.OUTGUID,
     TR.id,
      TYP.ORGANIZATION,
       rt.name,
    TR.NAME; 
   


SILO_NAME
OUTGUID
DEV_NAME
ID
ID_ORG
ORGANIZATION
MAX_TEMP
MIN_TEMP


S3
8
P8
8
1
TEST
17
11

S1
3
P3
3
1
TEST
18
15

S3
9
P9
9
1
TEST
19
10

S1
2
P2
2
1
TEST
13
11

S1
5
P5
5
1
TEST
19
18

S3
6
P6
6
1
TEST
17
11

S1
4
P4
4
1
TEST
13
11

S3
7
P7
7
1
TEST
16
11

S1
1
P1
1
1
TEST
13
12

S3
10
P10
10
1
TEST
17
11
SILO_NAME	OUTGUID	DEV_NAME	ID	ID_ORG	ORGANIZATION	MAX_TEMP	MIN_TEMP
S3	8	P8	8	1	TEST	17	11
S1	3	P3	3	1	TEST	18	15
S3	9	P9	9	1	TEST	19	10
S1	2	P2	2	1	TEST	13	11
S1	5	P5	5	1	TEST	19	18
S3	6	P6	6	1	TEST	17	11
S1	4	P4	4	1	TEST	13	11
S3	7	P7	7	1	TEST	16	11
S1	1	P1	1	1	TEST	13	12
S3	10	P10	10	1	TEST	17	11