Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query to get 3 record of latest dates

 
prajapatisagar Sagar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
 
prajapatisagar Sagar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


thanks for the reply but i want to use max(leave_applied_dt) show that it displays the max date first then others
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
prajapatisagar Sagar
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?


Thanks Martin the first reply helped be the problem is solved thanks bro!!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic