title: MYSQL get latest date
body: <p>i have query like this</p>
  
  <pre><code>SELECT seq, idtransaction, tanggal, tmptanggal, tgljatuhtempo,  
  @kodeku:=kodetb as kodetb, memberid, namadepan, namabelakang, tipekartu,nokk, 
  namabank, cf_1095, gesekan, amount, @ha:=if (seq=1, tmpkuranggesek-amount,@ha-
  amount) as kuranggesek   from
  ( SELECT 
   IFNULL(@subject,@subject := vtiger_servicecontracts.subject), IF(@subject &lt;&gt; 
  vtiger_servicecontracts.subject, @sec := 1, @sec := @sec+1) , @sec AS seq,
   IF(@subject &lt;&gt; vtiger_servicecontracts.subject, @subject := 
  vtiger_servicecontracts.subject, ''), 
  DATE_FORMAT(paymentdate ,'%d/%m/%Y') as tanggal, 
   DATE_FORMAT(due_date ,'%d/%m/%Y') as tgljatuhtempo, tmp.tanggal as tmptanggal,
  tmp.kuranggesek as tmpkuranggesek,idtransaction,
   vtiger_servicecontracts.subject as kodetb, @amount:=amount as amount,
   vtiger_servicecontracts.subject as subject, vtiger_contactdetails.contactid as memberid, campaignname as nokk, vtiger_contactdetails.firstname as namadepan, vtiger_contactdetails.lastname as namabelakang, vtiger_leaddetails.lastname as tipekartu, cf_1085 as namabank,cf_1095, amount as gesekan, @ha:=@ha-amount FROM `vtiger_accounting_payments`, vtiger_accounting ,vtiger_servicecontracts,vtiger_servicecontractscf, vtiger_vendorcf, vtiger_crmentity,vtiger_user2role, vtiger_role, vtiger_users,vtiger_leaddetails, vtiger_contactdetails, vtiger_campaign, (select @sec := 0, @subject := null    
  ) sel,  temp_kuranggesek_stafsda tmp 
  WHERE vtiger_crmentity.crmid=vtiger_accounting_payments.idtransaction 
  and vtiger_servicecontracts.subject=tmp.kodetb
  and vtiger_accounting_payments.machinename=vtiger_vendorcf.cf_1077
  and vtiger_accounting.accountingrelated2=vtiger_servicecontracts.servicecontractsid      and vtiger_servicecontractscf.servicecontractsid=vtiger_servicecontracts.servicecontractsid and accountingtype='Pelunasan Gesek' and 
  vtiger_accounting_payments.idtransaction=vtiger_accounting.accountingid and 
  vtiger_crmentity.deleted=0 and vtiger_user2role.userid=vtiger_users.id and 
  vtiger_crmentity.smcreatorid=vtiger_user2role.userid and   
  vtiger_user2role.roleid=vtiger_role.roleid and vtiger_users.user_name='staff' and cf_1091=leadid and  
  vtiger_contactdetails.contactid=vtiger_servicecontracts.contactid and 
  vtiger_campaign.campaignid=cf_1155 and date_format(paymentdate,'%Y-%m-%d') = date_format('2016-03-14','%Y-%m-%d') order by vtiger_servicecontracts.subject 
  )     
  AS temp
  </code></pre>
  
  <p>this query works successfully when table of tmp_kuranggesek_stafsda have data like this:</p>
  
  <pre><code>tanggal      kodetb    kuranggesek
  10-03-2016   1         600
  11-03-2016   2         550
  </code></pre>
  
  <p>but when one kodetb have two dates, such below:</p>
  
  <pre><code>tanggal      kodetb    kuranggesek
  10-03-2016   1         600
  11-03-2016   2         550
  11-03-2016   2         100
  </code></pre>
  
  <p>query will repeat displaying data as column tanggal because kodetb "2" has 2 dates.</p>
  
  <p>my question is:</p>
  
  <p>how to make this query can select only 1 date from table temp_kuranggesek_stafsda which  has the latest date of selected kodetb?</p>