How to get the latest date time record in an sql query with a where clause?

Question

I have two tables : RO_LAMEL_DATA and RO_MAIN_TABLE. RO_MAIN_TABLE includes all the serial numbers (serial_nr) for the productions which have a record key (record_key). RO_LAMEL_DATA has several records (on the same day) for each record key such as machine status (machine_status) with a date time value (pr_date_time). I want to get the latest machine status of one production. For this I do:

select a.machine_status
from ro_lamel_Data a inner join (
    select record_key, max(pr_date_time) as MaxDate
    from ro_lamel_Data
    group by record_key
) ro_main_table on (a.record_key = ro_main_table.record_key) and a.pr_date_time = MaxDate
where a.record_key =(
    select record_key from ro_main_table where serial_nr = 'Y39489');

However I get the error:

single-row subquery returns more than one row

How can I solve this? Thanks in advance!

Answer 1

Maybe you need something like

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

fiddle