Select MAX of each corresponding row

Question

new to SQL but I'll try to be clear,

I have 3 table, which have corresponding key columns. I need to select the row the MAX Value of a set column, for EACH of it's corresponding column.

Table1
 ID1  Value1
  1    Marie
  2    Max
  3    John

Table2
 ID2  Value2
  1    First
  2    Second
  3    Third

Table3
 ID1 ID2
  1   1
  1   2
  2   1
  2   2
  2   3
  3   1

So far I have something like so;

SELECT T1.Value1, T2.Value2 FROM Table1 T1
 INNER JOIN Table3 T3 ON T1.ID1 = T3.ID1
 INNER JOIN Table2 T2 ON T3.ID2 = T2.ID1
 WHERE (That's where I can't formulate correctly)

So far my tables are correctly joined, but I want to output only the rows where ID2 is at it's max value for the corresponding ID1.

So we'd have

And so, from correspondence of value we'd finally have.

Value1 Value2
 Marie  Second
 Max    Third
 John   First

Any help? Also, group by is prohibited.

Read up on `correlated subquery` to solve these types of problems without `group by` — Radagast, Mar 14 '22 at 15:59
@HoneyBadger Academic exercise, probably to get a better grasp of relational algebra within SQL. — Oninji, Mar 14 '22 at 16:01

Valeriu Ciuca · Accepted Answer · 2022-03-14T16:44:26.203

1

You must use MAX in WHERE condition:

SELECT
    T1.Value1,
    T2.Value2 
FROM
    Table1 T1
    INNER JOIN Table3 T3 ON T1.ID1 = T3.ID1
    INNER JOIN Table2 T2 ON T3.ID2 = T2.ID1 
WHERE
    T3.ID2 = (
        SELECT
            MAX(T3_alias.ID2) 
        FROM
            Table3 AS T3_alias 
        WHERE
            T3_alias.ID1 = T1.ID1
    )

edited Mar 14 '22 at 16:44

answered Mar 14 '22 at 15:58

Valeriu Ciuca

2,084
11
14

Select MAX of each corresponding row

1 Answers1