How to check another column value to be the highest to determine the latest record that occurs multiple times in Oracle SQL Developer?

Question

I have a table called ro_main_table which stores details of productions such as serial_nr, pr_date_time, machine_nr, record_key etc. I would like to get the distinct machine_nr and record key from ro_main_table where pr_date_time is in last 6 hours. For this I do:

select machine_nr, record_key, pr_date_time from ro_main_table where pr_date_time >= SYSDATE - 6/24;

Which gives me the table below:

MACHINE_NR	RECORD_KEY	PR_DATE_TIME
54	9809	17-DEC-20 04.02.35.000000000 AM
55	9811	17-DEC-20 04.58.22.000000000 AM
55	9817	17-DEC-20 09.17.50.000000000 AM
54	9814	17-DEC-20 07.57.24.000000000 AM
50	9818	17-DEC-20 09.45.22.000000000 AM

However, as you see there might be machines which are started twice during this time (i.e. machine_nr occurs multiple times). If this is the case, I will choose the record which has the highest record_key. For example, for machine 55 it is 9817. How can I achieve this?

Thank you very much in advance!

Answer 1

I found a way. I create an inner join like this:

select tt.machine_nr, tt.record_key, tt.pr_date_time 
from ro_main_table tt
INNER JOIN
    (SELECT machine_nr, MAX(record_key) AS MaxRecordKey
    FROM ro_main_table
    GROUP BY machine_nr) groupedtt 
ON tt.machine_nr = groupedtt.machine_nr 
AND tt.record_key = groupedtt.MaxRecordKey
where pr_date_time >= SYSDATE - 6/24;