query to get 3 record of latest dates

SELECT to_char(LEAVE_APPLIED_DT,'YYYY-MM-DD') as LEAVE_APPLIED_DT,
to_char(FROM_LEAVE_DT,'YYYY-MM-DD') as FROM_LEAVE_DT,
to_char(TO_LEAVE_DT,'YYYY-MM-DD') as TO_LEAVE_DT,
no_of_days,FN_EMPNAME(SUPERVISING_EMPLOYEE_CD) as suName,
FN_EMPNAME(SANCTIONING_EMPLOYEE_CD) as saName,reason,LEAVE_CD,LEAVE_TYPE,REMARKS,APPLY_STATUS
FROM employee_leave_apply WHERE ROWNUM < 4 and employee_cd = '843' order by LEAVE_APPLIED_DT desc

above is my query the problem is i want to display the latest leave_applied_dt but it displays what comes first please help the leave_applied_dt is date format can't use max function also to get maz leave_applied_dt.

Thanks in advance,Please help

In Oracle, rownums are assigned to rows before the order by step. You need to order the resultset first and then filter out the rows using rownum:

Martin Vajsar wrote:In Oracle, rownums are assigned to rows before the order by step. You need to order the resultset first and then filter out the rows using rownum:
select * from (SELECT to_char(LEAVE_APPLIED_DT,'YYYY-MM-DD') as LEAVE_APPLIED_DT, to_char(FROM_LEAVE_DT,'YYYY-MM-DD') as FROM_LEAVE_DT, to_char(TO_LEAVE_DT,'YYYY-MM-DD') as TO_LEAVE_DT, no_of_days,FN_EMPNAME(SUPERVISING_EMPLOYEE_CD) as suName, FN_EMPNAME(SANCTIONING_EMPLOYEE_CD) as saName,reason,LEAVE_CD,LEAVE_TYPE,REMARKS,APPLY_STATUS FROM employee_leave_apply WHERE and employee_cd = '843' order by LEAVE_APPLIED_DT desc) where ROWNUM < 4

thanks for the reply but i want to use max(leave_applied_dt) show that it displays the max date first then others

prajapatisagar Sagar wrote:thanks for the reply but i want to use max(leave_applied_dt) show that it displays the max date first then others

I don't understand your requirement. Do you want just to independently verify that the query does indeed return the max date first?

Martin Vajsar wrote:

prajapatisagar Sagar wrote:thanks for the reply but i want to use max(leave_applied_dt) show that it displays the max date first then others

I don't understand your requirement. Do you want just to independently verify that the query does indeed return the max date first?
select max(LEAVE_APPLIED_DT) from employee_leave_apply

Thanks Martin the first reply helped be the problem is solved thanks bro!!!