CNT.EMPLID, CNT.ONTRACT_NUM,CNT.START_DATE, CNT.END_DATE FROM
INNER JOIN PS_CAATNTFY_TMP TMP ON CNT.EMPLID = TMP.EMPLID
AND CNT.START_DATE = TMP.START_DATE
-- This yields results like :
1000926 118329 2013-05-01 00:00:00.000 2013-08-30 00:00:00.000
1000926 118330 2013-05-01 00:00:00.000 2014-04-01 00:00:00.000
I want only the second row where the maximum end-date is 2014-04-01. Without having to do a second pass of the data in another query, how would I do this as part of the above query.
Any help would be greatly appreciated,