Getting Max Date from Query in SQL

Question

Im a beginner using SQL and I am wondering if someone could help. I have 2 Tables Table 1 is Customer Table like the below

CUSTOMER ID - CUSTOMER JOIN DATE
1234               01/03/2018
2345               21/07/2019

Table 2 is a Value Segment

CUSTOMER ID - VALUE - LOADED_DATE
1234          HIGH     01/09/20
1234          MEDIUM   01/10/20
2345          LOW      01/09/20
2345          LOW      01/10/20

What I need to do is join the Customer table and bring back the Customer ID and Joined Date and then bring back the value from the Value Segment table which has the latest Loaded date so my table would look like below

CUSTOMER ID - CUSTOMER JOIN DATE   - VALUE
1234               01/03/2018        MEDIUM ( as it was loaded on 01/10/20)
2345               21/07/2019        LOW

Thanks in advance

Thanks, Mike

Answer 1

One option could be using row_number()

select * from
(
select cusomer_id,customer_join_date, value,row_number() over(partition by customer_id order by LOADED_DATE desc) as rn
from 
customer c inner join value_segment v on c.customer_id=v.customer_id
)A where rn=1

Answer 2

With FIRST_VALUE() window function:

select distinct c.*,
  first_value(v.value) over (partition by c.customerid order by v.loaded_date desc) value
from customer c inner join valuesegment v
on v.customerid = c.customerid