Dany Dany - 5 months ago 37
SQL Question

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 | M_ID
1 | 'sub1-1' | 1 | 1
2 | 'sub1-2' | 2 | 1
3 | 'sub1-3' | 3 | 1
4 | 'sub2-1' | 1 | 2
5 | 'sub2-2' | 2 | 2
6 | 'sub2-3' | 3 | 2
7 | 'sub3-1' | 1 | 3
8 | 'sub3-2' | 2 | 3
9 | 'sub3-3' | 3 | 3


I need to join these two tables (by
M_ID
) but from the
Sub-table
I need only the row with the largest value of
S_ORDER
.

So the expected result of the query is:

M_ID | M_FIELD | S_FIELD
1 | 'main1' | 'sub1-3'
2 | 'main2' | 'sub2-3'
3 | 'main3' | 'sub3-3'


There is working solution with analytical function in the answer of this question: How do I limit the number of rows returned by this LEFT JOIN to one?
(I will post it at the bottom)
But the problem is that
Sub-Table
is very big (and is actually a view with some inner calculations) and this kind of subquery works way too long. So I suppose I need to filter out the table by m_id first and only after that find the field with the largest
S_ORDER


I need something simple like this (which fails because the second level subquery doesn't see the
M.M_ID
field outside):

SELECT m.*,
(SELECT s_field
FROM (SELECT s_field
FROM t_sub s
WHERE s.m_id = m.m_id
ORDER BY s_order DESC)
WHERE ROWNUM = 1) s_field
FROM t_main m;


The code to create and populate the test schema:

CREATE TABLE t_main (m_id NUMBER PRIMARY KEY,
m_field VARCHAR2(10));
CREATE TABLE t_sub (s_id NUMBER PRIMARY KEY,
s_field VARCHAR2(10),
s_order NUMBER,
m_id NUMBER );
INSERT INTO t_main VALUES (1,'main1');
INSERT INTO t_main VALUES (2,'main2');
INSERT INTO t_main VALUES (3,'main3');
INSERT INTO t_sub VALUES (1,'sub1-1', 1, 1);
INSERT INTO t_sub VALUES (2,'sub1-2', 2, 1);
INSERT INTO t_sub VALUES (3,'sub1-3', 3, 1);
INSERT INTO t_sub VALUES (4,'sub2-1', 1, 2);
INSERT INTO t_sub VALUES (5,'sub2-2', 2, 2);
INSERT INTO t_sub VALUES (6,'sub2-3', 3, 2);
INSERT INTO t_sub VALUES (7,'sub3-1', 1, 3);
INSERT INTO t_sub VALUES (8,'sub3-2', 2, 3);
INSERT INTO t_sub VALUES (9,'sub3-3', 3, 3);
COMMIT;


Working solution mentioned above (working too slow with large
T_SUB
table):

SELECT m.*,
s.s_field
FROM t_main m
LEFT JOIN
(SELECT *
FROM
(SELECT ts.*,
ROW_NUMBER() OVER (PARTITION BY m_id
ORDER BY s_order DESC) AS seq
FROM t_sub ts)
WHERE seq = 1) s ON s.m_id = m.m_id;


The DB we use is Oracle 10g

Thank you very much for your help

Answer

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