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!

score 0 · Answer 1 · answered Dec 17 '20 at 09:35

0

select machine_nr, max(record_key), pr_date_time from ro_main_table where pr_date_time >= SYSDATE - 6/24 group by machine_nr;

this query should get the result you wish

answered Dec 17 '20 at 09:35

Yanick Palmers

1
2

I get the error >ORA-00979: not a GROUP BY expression – Eda Dec 17 '20 at 09:39
select machine_nr, max(record_key), pr_date_time from ro_main_table where pr_date_time >= SYSDATE - 6/24 group by record_key; should work – Yanick Palmers Dec 17 '20 at 09:53

score 0 · Accepted Answer · answered Dec 17 '20 at 09:53

0

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;

answered Dec 17 '20 at 09:53

Eda

93
9

Instead, you can use the window function as mentioned in my answer. This query will not work if two records have the same max `pr_date_time ` for the same `machine_nr` – Popeye Dec 17 '20 at 10:21

score 0 · Answer 3 · answered Dec 17 '20 at 10:00

You can use the window function as follows:

select * from 
(select machine_nr, record_key, pr_date_time 
       row_number() over (partition by machine_nr order by record_key desc) as rn
from ro_main_table where pr_date_time >= SYSDATE - 6/24)
where rn = 1;

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

3 Answers3