select *,row_number() over(partition by id order by dt ) as r from file_a; select * from () as a where r =1 ; select coalesce(a.id,b.id) as id ,a.name,b.code from table1 as a full join table2 as b on a.id = b.id order by id ;