Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ro_lamel_Data (machine_status INT, record_key INT, pr_date_time INT);
CREATE TABLE ro_main_table (record_key INT);
INSERT INTO ro_lamel_Data
SELECT 111,11,1 FROM DUAL UNION ALL
SELECT 112,11,2 FROM DUAL UNION ALL
SELECT 113,11,3 FROM DUAL UNION ALL
SELECT 211,12,1 FROM DUAL UNION ALL
SELECT 212,12,2 FROM DUAL ;
5 rows affected

INSERT INTO ro_main_table
SELECT 11 FROM DUAL UNION ALL
SELECT 12 FROM DUAL;
2 rows affected

SELECT * FROM ro_main_table;


RECORD_KEY


11

12


SELECT * FROM ro_lamel_Data;


MACHINE_STATUS
RECORD_KEY
PR_DATE_TIME


111
11
1

112
11
2

113
11
3

211
12
1

212
12
2


SELECT ro_lamel_Data.* 
FROM ro_main_table 
JOIN ro_lamel_Data ON ro_lamel_Data.record_key = ro_main_table.record_key
ORDER BY ro_main_table.record_key, ro_lamel_Data.pr_date_time;


MACHINE_STATUS
RECORD_KEY
PR_DATE_TIME


111
11
1

112
11
2

113
11
3

211
12
1

212
12
2


WITH cte AS ( SELECT machine_status, 
                     record_key,
                     ROW_NUMBER() OVER (PARTITION BY record_key 
                                        ORDER BY pr_date_time DESC) rn
              FROM ro_lamel_Data )
SELECT cte.record_key, cte.machine_status last_status
FROM cte 
JOIN ro_main_table ON cte.record_key = ro_main_table.record_key
WHERE rn = 1


RECORD_KEY
LAST_STATUS


11
113

12
212