Leftjoinでキーが2つあり、2つ目のキーの最大値だけをつなげたい場合
select * from D_CONTRACT
left join D_ORDER
on CRNOCR = ODNOCR
and ODNOOD = (select max(ODNOOD) from D_ORDER as D_ORDER2 where D_ORDER2.ODNOCR = D_ORDER.ODNOCR )
Joinの条件に対象のキーの最大値を含める
select * from D_CONTRACT
left join D_ORDER
on CRNOCR = ODNOCR
and ODNOOD = (select max(ODNOOD) from D_ORDER as D_ORDER2 where D_ORDER2.ODNOCR = D_ORDER.ODNOCR )
Joinの条件に対象のキーの最大値を含める