Results 1 to 5 of 5

Thread: Query Help

  1. #1
    Join Date
    Aug 2002
    Posts
    121

    Question Unanswered: Query Help

    Hello,
    In a table with the fields emp_id, and review_date; I'm trying to find the second most recent review_date for an employee. I know how the find the most recent review_date, simply by....

    SLEECT emp_id, MAX(review_date)
    FROM employees
    GROUP BY emp_id;

    ...but I'm a little puzzled as to how to find the next most recent review date for an employee. It seems simple but I'm having a brain block on this one for some reason. Can anyone assist?

  2. #2
    Join Date
    Jul 2007
    Posts
    2
    try this...

    select max(review_date)
    from employees
    where review_date<(select max(review_date) from employees)

  3. #3
    Join Date
    Aug 2002
    Posts
    121
    Quote Originally Posted by latha.jeyaraman
    try this...

    select max(review_date)
    from employees
    where review_date<(select max(review_date) from employees)
    Thanks Latha... but your result still returns the
    MAX(review_date), as opposed to the 2nd most recent review_date. It's odd, I thought initially that this was easy; perhaps it's a little more complex.
    Last edited by tmalone; 07-05-07 at 13:48.

  4. #4
    Join Date
    Jul 2007
    Posts
    2
    1. try using rank() function.
    2. check if you need to playaround with to_char() & to_date() functions.

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Try
    Code:
    SLEECT emp_id, MAX(review_date)
    FROM employees e
     where review_date < ( select max( review_date ) from employees where emp_id = e.emp_id )
    GROUP BY emp_id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •