Oracle: Left Join Very Big Table And Limit The Joined Rows To One With The Largest Field Value

I have two tables. The second one references to the first one by m_id. Main table M_ID | M_FIELD 1 | 'main1' 2 | 'main2' 3 | 'main3' Sub-table S_ID | S_FIELD | S_ORDER |

Solution 1:

try this

SELECT m.*,
       (select s.s_field 
          from t_sub s
         where s.m_id = m.m_id
           and s.s_order = (select max(s_order) from t_sub where t_sub.m_id = s.m_id)
           and rownum = 1)
FROM t_main m

or you can try this (it's your code but some modifications)

SELECT m.*,
      (select s.s_field from 
       (SELECT s_field, m_id
          FROM t_sub
         --where t_sub.m_id = m.m_id
         order by s_order DESC) s
        where s.m_id = m.m_id
          and rownum = 1)
FROM t_main m

Solution 2:

select t.*, s.s_field from t_main t
left join (select m_id, min(s_field) keep(dense_rank first order by s_order desc) as s_field 
       from t_sub group by m_id) s on (s.m_id = t.m_id)