Second Latest Date per Group

[sugargirl]

I have a table with these fields: employee ID, effective on date and supervisor ID.

Whenever an employee's supervisor changes, a new record is created with these fields. I need to retrieve the prior supervisor's ID for each employee. How would I pull the second latest effective on date for each employee?

[debasisdas]

what is the query that you are working on ?

[OraMaster]

@sugargirl
Please try to post sample data and expected output henceforth. If I understood your req. correctly then I suggest below SQL to get the expected result.

SELECT *
  FROM (SELECT employee_id, effective_on_date, supervisor_id,
               ROW_NUMBER () OVER (PARTITION BY employee_id ORDER BY effective_on_date DESC)
                                                                       seqnum
          FROM empsupv)
 WHERE seqnum = 2

[sugargirl]

OraMaster,

Thanks so much for your post. The example you provided worked wonderfully!

Thanks!