SELECT
 a.id AS `Account ID`,
 MAX(s.`date`) AS `Suspension Date`,
 a.client_id AS `Client ID`,
 CONCAT(cd.firstname,' ',cd.lastname) AS `Client`,
 a.`domain` AS `Account Domain`,
 a.date_created AS `Creation date`,
 a.billingcycle AS `Billing cycle`,
 a.username AS `Username`
FROM
     hb_accounts a JOIN
     hb_client_details cd ON (cd.id=a.client_id) JOIN
     `hb_account_logs` s ON (s.account_id=a.id)
WHERE
     a.status='Suspended'
    AND s.`event`='AccountSuspend' 
    AND s.`result`='1'
GROUP BY a.id
ORDER BY `Suspension Date` DESC