Как использовать несколько максимальных функций в SQL select с левым соединением

Select t1.id, t1.value1, max(t2.date_1) date_1, max(t3.length_1)t3
from table_1 t1
left join table_2 t2 on t1.id=t2.id
left join table_3 t3 on t1.id=t3.id
group by t1.id, t1.value1
order by 1

Агрегируйте, прежде чем присоединиться к столам:

SELECT t1.id,
       t1.value_1,
       t2.date_1,
       t3.length_1
FROM   table1 t1
       INNER JOIN (
         SELECT id,
                MAX(date_1) AS date_1
         FROM   table2
         GROUP BY id
       ) t2
       ON (t1.id = t2.id)
       INNER JOIN (
         SELECT id,
                MAX(length_1) AS length_1
         FROM   table3
         GROUP BY id
       ) t3
       ON (t1.id = t3.id)

Что для примера данных:

CREATE TABLE Table1 (ID, Value_1) AS
SELECT 11, 'abc' FROM DUAL UNION ALL
SELECT 22, 'def' FROM DUAL UNION ALL
SELECT 33, 'xyz' FROM DUAL;

CREATE TABLE Table2 (ID, Date_1) AS
SELECT 11, DATE '2022-03-12' FROM DUAL UNION ALL
SELECT 11, DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 22, DATE '2022-12-19' FROM DUAL UNION ALL
SELECT 22, DATE '2023-02-07' FROM DUAL UNION ALL
SELECT 33, DATE '2022-03-07' FROM DUAL;

CREATE TABLE Table3 (ID, Length_1) AS
SELECT 11,  574 FROM DUAL UNION ALL
SELECT 11, 1029 FROM DUAL UNION ALL
SELECT 22, 9220 FROM DUAL UNION ALL
SELECT 33, 1093 FROM DUAL UNION ALL
SELECT 33,  876 FROM DUAL;

Выходы:

рабочий пример

Один из вариантов — использовать подзапросы, выбирающие максимальные значения из таблиц 2 и 3:

Select      t1.ID, t1.VALUE_1, 
            (Select Max(DATE_1) From Table2 Where ID = t1.ID) "DATE_1",  
            (Select Max(LENGTH_1) From Table3 Where ID = t1.ID) "LENGTH_1"
From        Table1 t1
Order By    t1.ID

... еще один - использовать аналитическую функцию с отдельным ключевым словом, но это может быть дорого с большими наборами данных:

Select  DISTINCT
            t1.ID, t1.VALUE_1, Max(t2.DATE_1) OVER(Partition By t1.ID) "DATE_1",  Max(t3.LENGTH_1) OVER(Partition By t1.ID) "LENGTH_1"
From        Table1 t1
Inner Join  Table2 t2 ON(t2.ID = t1.ID)
Inner Join  Table3 t3 ON(t3.ID = t1.ID)
Order By    t1.ID

... оба с вашими образцами данных:

WITH 
    Table1 (ID, VALUE_1) AS
        (
            Select 11, 'abc' From Dual Union All
            Select 22, 'def' From Dual Union All
            Select 33, 'xyz' From Dual
        ),
    Table2 (ID, DATE_1) AS
        (
            Select 11, To_Date('2022-03-12', 'yyyy-mm-dd') From Dual Union All
            Select 11, To_Date('2023-01-01', 'yyyy-mm-dd') From Dual Union All
            Select 22, To_Date('2022-12-19', 'yyyy-mm-dd') From Dual Union All
            Select 22, To_Date('2023-02-07', 'yyyy-mm-dd') From Dual Union All
            Select 33, To_Date('2022-03-07', 'yyyy-mm-dd') From Dual
        ),
    Table3 (ID, LENGTH_1) AS
        (
            Select 11,  574 From Dual Union All
            Select 11, 1029 From Dual Union All
            Select 22, 9220 From Dual Union All
            Select 33, 1093 From Dual Union All
            Select 33,  876 From Dual   
        )

результаты как:

        ID VALUE_1 DATE_1      LENGTH_1
---------- ------- --------- ----------
        11 abc     01-JAN-23       1029 
        22 def     07-FEB-23       9220 
        33 xyz     07-MAR-22       1093