[SOLVED] Select MAX of each corresponding row

Issue

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

ID1 ID2
 1   2
 2   3
 3   3

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.

Solution

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
    )

Answered By – Valeriu Ciuca

Answer Checked By – Katrina (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.