数据库select最大值

select kou.*,syo.productname,g.gouinfo,jya.jyanruname,kik.planname
from koukoku kou
join (select max(kk.contractsubcode) as maxcontractsubcode,kk.contractcode,kk.adcode from koukoku kk
group by kk.contractcode,kk.adcode) max
on max.contractcode = kou.contractcode and max.maxcontractsubcode = kou.contractsubcode and max.adcode = kou.adcode
join syouhin syo on
kou.productcode = syo.productcode
join gou g on
g.goucode = kou.goucode and g.productcode = kou.productcode
join kikaku kik on
kik.plancode = kou.plancode and kik.productcode = kou.productcode
left join jyanru jya on
jya.jyanrucode = kou.genre
where kou.adcode like #adcode# and kou.contractcode = #contractcode#
$orderby$