SQL - Oracle (Get second highest date by group)

[piyushdabomb]

Hi All,

Currently, I have a table with 2 columns COMPONENTID and COMPLETIONDATE

COMPONENTID COMPLETIONDATE
CBT_HAZCOM 02/26/2007 15:17:00
CBT_HAZCOM 07/31/2007 21:23:00
QS 02/12/2007 13:51:00
SHEN158 02/12/2007 13:29:00

What I want to do from this table is grab the second latest date under the completiondate column ID grouped by the COMPONENTID. If there is only 1 entry for COMPONENTID (like for QS and SHEN158), it should keep the current COMPLETIONDATE

The resulting output should be:

COMPONENTID COMPLETIONDATE
CBT_HAZCOM 02/26/2007 15:17:00
QS 02/12/2007 13:51:00
SHEN158 02/12/2007 13:29:00

NOTE: CBT_HAZCOM with COMPLETIONDATE removed 07/31/2007 because it was the latest date in the CBT_HAZCOM grouping. The second latestdate is 02/26/2007.

Please help as this is critical in what I am trying to accomplish!

Sincerely,

Piyush A.

[debasisdas]

Can you kindly post what / how you have tried to solve this problem.

[piyushdabomb]

Here is the SQL code I used to solve the problem. This only gives the MAX date and not the second max date. I have no clue as to how to capture the second max date.

select A.componentid, A.completiondate
from
(
select pa_cpnt_evthst.cpnt_id as componentid,MAX (pa_cpnt_evthst.compl_dte) as completiondate
FROM pa_cpnt_evthst, pa_cpnt
WHERE pa_cpnt_evthst.cpnt_id = pa_cpnt.cpnt_id
AND pa_cpnt_evthst.stud_id = '10203996'
AND pa_cpnt.notactive = 'N'
AND pa_cpnt.retrng_int IS NOT NULL
AND pa_cpnt.retrng_int <> 0
AND ((pa_cpnt_evthst.cpnt_typ_id = 'CBT'AND pa_cpnt_evthst.cmpl_stat_id IN ('09', '10'))OR (pa_cpnt_evthst.cpnt_typ_id <> 'CBT'))
group by pa_cpnt_evthst.cpnt_id
) A


Help as this is urgent. Any assistance is VERY much appreciated.