SELECT R.res_clt_id                                        CLT_ID, 
       R.res_id                                            RES_ID, 
       (SELECT 'YES' 
        FROM   dual 
        WHERE  R.res_id = (SELECT Max(lll.res_id) 
                           FROM   dbxxx lll 
                           WHERE  lll.res_clt_id = r.res_clt_id 
                                  AND lll.deleted <> 'Y')) FLAG 
FROM   dbxxx R 
WHERE  R.deleted <> 'Y' 
       AND R.res_startdt IS NOT NULL;