query to get 3 record of latest dates

[prajapatisagar Sagar]

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

[Martin Vashko]

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

[prajapatisagar Sagar]

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

[Martin Vashko]

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

[prajapatisagar Sagar]

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!!!