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.
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)