Say we have two tables performing a left join:
Table 1
Joint Key || Attribute 1 || Attribute 2 || Attribute 3
A 1 11 21
B 2 12 22
C 3 13 23
Table 2
Joint Key || Attribute 4 || Attribute 5
A 31 41
A 32 42
C 33 43
by performing a table 1 left join table 2 on "Joint Key"
it will return two records having
Joint Key = 'A'
Joint Key || Attribute 1 || Attribute 2 || Attribute 3 || Attribute 4 || Attribute 5
A 1 11 21 31 41
A 1 11 21 32 42
What's the best practice of defining the return police, specifically in snowflake, that can return me the same row count as table 1.
Taking the above example, I want the the record has the MAX(Attribute 4)
. Two initial ideas come to my mind
Option 1: use "GROUP BY" clause -- need list columns explicitly, cumbersome when dealing with table has many columns.
Option 2: something like
select * from (
select
Tabel1.*
max(Table2.Attribute_4) as mx_Attribute_4,
Table2.Attribute_5
from Table1
left join Table2
on Joint_Key
) as temp
where temp.Attribute_4 = temp.mx_Attribute_4
it's quite complicated and time-consuming too.
Any other suggestions?