postgresql - Join Help Needed -
i have postgresql question think simple i'm having trouble conceptualizing joins need achieve this.
i have table in format:
company agreement_num end_of_agreement contract_amount abc 2 (null) (null) abc (null) 2015-01-10 10 abc (null) 2015-09-01 12 acme 2 (null) (null) acme (null) 2014-06-05 5
i table this:
company agreement_num end_of_agreement contract_amount abc 2 (null) (null) abc 2 2015-01-10 10 abc 2 2015-09-01 12 acme 2 (null) (null) acme 2 2015-06-05 5
so basically, insert agreement_num corresponding company.
so know i'm going, after this, i'll clause grab rows haven't reached end_of_agreement:
company agreement_num end_of_agreement contract_amount abc 2 2015-09-01 12 acme 2 2015-06-05 5
thank you!
i think use like:
select t1.company, t2.agreement_num, t1.end_of_agreement, t1.contract_amount table inner join ( select company, agreement_num table agreement_num not null ) t2 on t1.company = t2.company the subquery fetch agreement_num each company , use in main query.
you use windowing functions pull off makes smaller query:
select company, max(agreement_num) on (partition company) agreement_num, end_of_agreement, contract_amount table;