-1

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!

MT0
  • 143,790
  • 11
  • 59
  • 117
Eda
  • 93
  • 9

2 Answers2

2

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

Akina
  • 39,301
  • 5
  • 14
  • 25
  • What is cte and rn in this case? – Eda Dec 09 '20 at 10:13
  • @Eda `cte` is Common Table Expression alias. `rn` is column alias. – Akina Dec 09 '20 at 10:14
  • After you edited, now it says: "CTE"."RECORD_KEY": invalid identifier, Error at Line: 7, what can I do? – Eda Dec 09 '20 at 10:20
  • @Eda Corrected. Fiddle added. – Akina Dec 09 '20 at 10:26
  • Great, it complies, but instead of having the list of all record keys, I just want to have one specific which is the same as `record_key` of a `serial_nr` (e.g. 'Y12134') from `ro_main_table`. Is this possible? – Eda Dec 09 '20 at 10:32
  • 1
    @Eda Nothing prevents to add WHERE clause(s) with according condition(s). – Akina Dec 09 '20 at 10:34
0

If you want one row, the use order by and fetch first:

select ld.machine_status
from ro_lamel_Data ld join
     ro_main_table mt
     using (record_key)
where mt.serial_nr = 'Y39489'
order by ld.pr_ate_time desc
fetch first 1 row only;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786