SELECT EMPPLCY,PLCYINCEPDT FROM TABLE
WHERE PLCYINCEPDT = (SELECT MAX(PLCYINCEPDT)
FROM TABLE WHERE EMPPLCY LIKE '789AB%');
I hope there is only 1 row in all the table where plcyincepdt = '2010-01-01'
otherwise this fails miserably. the normal construct is as follows :
Code:
SELECT EMPPLCY,PLCYINCEPDT FROM TABLE1 A
WHERE a.EMPPLCY LIKE '789AB%'
AND a.PLCYINCEPDT = (SELECT MAX(b.PLCYINCEPDT)
FROM TABLE1 b WHERE b.EMPPLCY = a.empplcy);
