oracle - SQL - How to select row by compare date from 2 table -
i have 2 table that:
table1:
id | company_name | rank | first_regist_date 1 1 2017-09-01 2 b 2 2017-09-05
table 2:
id | company_name | rank | first_regist_date 1 3 2017-09-03 2 c 4 2017-09-04
i need select company data first_regist_date , rank in case of company have 2 first regist date, choose earlier date , rank greater (ex: company above have first date: 2017-09-01)
the expect result that: company - rank 3 - date:2017-09-01
please have me select in case
this technically answers question avoids elephant in room (which id takes preference?). both tables have id's may overlap ({b,c} have id of 2) rules need defined id takes preference other table id's renamed to.
select company_name ,min(first_regist_date) regist_date ( select * #table1 union select * #table2 ) t3 group company_name
op dont change question (by adding rank) after question has been answered. changes: kindly contributed @toha
select company_name ,min(first_regist_date) regist_date ,max(rank ) rank ( select * #table1 union select * #table2 ) t3 group company_name
Comments
Post a Comment