This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes query to get 3 record of latest dates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "query to get 3 record of latest dates" Watch "query to get 3 record of latest dates" New topic
Author

query to get 3 record of latest dates

prajapatisagar Sagar
Ranch Hand

Joined: Feb 28, 2011
Posts: 35
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

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Feb 28, 2011
Posts: 35
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

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Feb 28, 2011
Posts: 35
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!!!
 
Don't get me started about those stupid light bulbs.
 
subject: query to get 3 record of latest dates
 
Similar Threads
SQLException Cursor Closed
Incorrect Colum/Size Error while converting from java to excel
query in oracle
problem with Date and timestamp
How to write to TO_CHAR and TO_DATE in HQL