Getting the second latest date in a group

[piyushdabomb]

Hi All,

Currently, I have a SQL query where I need to grab the SECOND latest date by group.

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 CBT_HAZCOM. 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

Please note that 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.

[piyushdabomb]

Sorry for the miscommunication. The wording may have been a little off:

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

[pbmods]

Heya, Piyush.

If getting the latest date looks something like this:

SELECT
        `COMPONENTID`,
        `COMPLETIONDATE`
    FROM
        `Table`
    ORDER BY
        `COMPLETIONDATE` DESC
    LIMIT 1;
 

Then getting the second-to last date looks like this (note the LIMIT clause):

SELECT
        `COMPONENTID`,
        `COMPLETIONDATE`
    FROM
        `Table`
    ORDER BY
        `COMPLETIONDATE` DESC
    LIMIT 1,1;
 

The first number tells MySQL to offset the results by one. The second number tells MySQL to return only one result.

[piyushdabomb]

Very much appreciated, however is the LIMIT command only for My SQL? Currently, I need my code universal to Oracle Databases and the Limit 1,1 will not work.

Is there an alternative without having to use LIMIT 1,1? I have spent numerous hours trying to figure this out; any help is appreciated.

This is the SQL code I used to determine the max value:

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

[pbmods]

Heya, piyushdabomb.

I'm going to go ahead and move this thread to the Oracle forum, where our resident Experts will be better able to help you out.

[Dave44]

Try this out. It uses an analytic function to number the rows by date desc, grouped by the component. then i simply limit the count to 2 rows per group and take the minimum.

SELECT   MIN( componentid) componentid,
         MIN( completiondate) completiondate
FROM     (SELECT componentid,
                 completiondate,
                 ROW_NUMBER() OVER(PARTITION BY componentid ORDER BY completiondate DESC) my_row_num
          FROM   temp)
WHERE    my_row_num <= 2
GROUP BY componentid;
 

[piyushdabomb]

Dave44:

After much research you've brought a ton of insight as to using "rows" instead of playing with subselects and outerjoins...Online Analytical Processes are definitely the way to go (from your suggestions).

My questions now bear:-

> Do you have any good links that could help me optimize my code?
> How do I know for example that an outerjoin is going to be quicker than using windows functions?
> What difficulty level would you classify Analytical processes if we were to categorize them - Beginner, Intermediate, Above Average, Guru

I seem to use a TON of subselects but also takes my query 3 minutes to run. Potentially, I could increase the speed of my queries if I knew the correct alternatives.