SELECT a.*, (select count(*) from jobvacancy b where a.corporate_id = b.corporate_id and b.status_publish in ('Activate', 'Reactivate') and b.status = 'Publish') as jml_job, c.logo FROM corporate_profile a left join corporate_document c on a.corporate_id = c.corporate_id left join registercr xx on a.corporate_id = xx.corporate_id where a.publishcomp = 'Publish' AND xx.status = 'activated' group by a.corporate_id ORDER BY CASE WHEN a.corporate_user_admin = 'CR-U-AA-00059' THEN 1 ELSE 0 END, xx.wakturegister desc LIMIT 30, 15