Selecting columns from tables based on max value of another column

I have two tables and I want column input from first_table and column output from second_table based on the latest finished time

first_table:

id    input    
--------------
1     America    
2     China      
3     Russia     
2     China      
3     Russia     

second_table

id    output     finished_time
-------------------------------------------------
1     Washington    10/5/2019 10:05:13 PM +00:00
2     Shanghai      10/6/2019 10:05:13 PM +00:00
3     Kazan         10/7/2019 10:05:13 PM +00:00
2     Beijing       10/10/2019 10:05:13 PM +00:00
3     Moscow        10/11/2019 10:05:13 PM +00:00

result_table

id    input      output  
-----------------------------
1     America    Washington
2     China      Beijing
3     Russia     Moscow

I am trying to use this query:

SELECT input, second_table.output
FROM first_table
INNER JOIN second_table ON first_table.id = second_table.id 
                        AND Max(finished_time)

Answer

You can use a correlated subquery in the on clause of the join:

select
    f.*,
    s.output
from first_table f
inner join second_table s
    on  s.id = f.id
    and s.finished_time = (
        select max(s1.finished_time)
        from second_table s1
        where s1.id = s.id
    )

For performance here, you want an index on second_table(id, finished_time).

This can also be expressed with a not exists condition:

select
    f.*,
    s.output
from first_table f
inner join second_table s 
    on  s.id = f.id
    and not exists (
        select 1 
        from second_table s1
        where s1.id = s.id
        and s1.finished_time > s.finished_time

    )

Finally, another option is to use a window function for ranking

select id, input, output
from (
    select
        f.*,
        s.output,
        rank() over(partition by s.id order by s.finished_time desc) rn
    from first_table f
    inner join second_table s on s.id = f.id
) x
where rn = 1

You can play around with the solutions and pick the one that you understand better, or that performs faster.